﻿'DBFL02


Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.ObjectModel
Imports System.Text


Public Class CFLServicioLocalSQLServerDB

   
 Public Const __CLAVE_CAT_INTERMEDIARIO__          As Byte = 6
 Public Const __CLAVE_CAT_ESTATUS_PS__             As Byte = 9
 Public Const __CLAVE_CAT_ESTATUS_FOLIO_DEC__      As Byte = 12
 Public Const __CLAVE_CAT_ESTATUS_DISTRIB_PEJ__    As Byte = 7
 Public Const __CLAVE_CAT_TVNG__                   As Byte = 5
 Public Const __CLAVE_CAT_FECHA_PERIODO_EJ__       As Integer = 58

   
    Public Shared Function ModificarFechaBSO(ByVal fecha As Date, ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer


        sSql = "update bso" _
             & " set bso.ESTATUS_BSO = " & CTBSO.enumEstatusBSO.CANCELADA _
             & ", bso.USU_GESTOR = " & CServiciosMacrosSyBaseDB.IDUSUARIO _
             & " from FL_OP_BSO bso" _
             & " where bso.ESTATUS_BSO = " & CTBSO.enumEstatusBSO.ABIERTA

        sSql = sSql & ";"

        sSql = sSql & "; update pa" _
             & " set pa.PARAMETRO_VALOR = '" & fecha.ToString("yyyy-MM-dd") & "'" _
             & " from MMA_PARAMETROS pa" _
             & " where pa.ID = 57"

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function CancelarFechaBSO(ByVal fecha As Date, ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        

        sSql = "update bso" _
             & " set bso.ESTATUS_BSO = " & CTBSO.enumEstatusBSO.CANCELADA _
             & ", bso.USU_GESTOR = " & CServiciosMacrosSyBaseDB.IDUSUARIO _
             & " from FL_OP_BSO bso" _
             & " where bso.ESTATUS_BSO = " & CTBSO.enumEstatusBSO.ABIERTA _
             & " and convert(varchar(10),bso.FEC_OPERA,103) = '" & fecha.ToString("dd/MM/yyyy") & "'"

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function ObtieneCatalogo(ByVal IdCat As Integer) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtCatalogo As New DataTable()

        Try
            sSql = "SELECT * FROM GEN_CAT_GEN WHERE ID_CAT = " & IdCat
            
            dtCatalogo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtCatalogo

        Catch ex As Exception
    
            Throw ex
        End Try

    End Function

    Public Shared Function ObtieneFondos(ByVal IdCatSolicitud As Integer, ByVal IdClaveSolicitud As Integer) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFondos As New DataTable()

        Try
            sSql = "SELECT * FROM FL_REL_FONDO_TVNG WHERE ID_TVNG = " & IdClaveSolicitud & " AND CAT_TVNG = " & IdCatSolicitud
            
            dtFondos = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtFondos

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Shared Function ObtieneBSOActivo() As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtBSO As New DataTable()

        Try
            sSql = "select bso.* " _
                   & " from FL_OP_BSO bso" _
                   & " where BSO.ESTATUS_BSO = " & CTBSO.enumEstatusBSO.ABIERTA


            dtBSO = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtBSO

        Catch ex As Exception            

            Throw ex
        End Try

    End Function

     Public Shared Function ObtienePartidasSolicitud(ByVal IdBSO As Integer) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtPS As New DataTable()
        Try
            sSql = "select ps.ID_PARTIDA_PS, bso.ESTATUS_PARTIDA, bso.CANTIDAD_ACTUAL, " _
                   & "ps.SEC_PARTIDA, ps.TVNG, ps.FEC_SOLICITUD, ps.FEC_LIQUIDACION, " _
                   & "ps.FEC_PLAZO, ps.CANTIDAD_SOL, ps.COMENTARIO, ps.TIPO_CANTIDAD, " _
                   & "ps.PLAZO, ep.VALOR ESTATUS_VALOR, tv.VALOR TV_VALOR, " _
                   & "(select count(*) " _
                   & "from FL_OP_PEJ pej, FL_REL_PS_PEJ rpe, FL_REL_DISTRIB_PEJ di " _
                   & "where pej.ID_PARTIDA_EJ = rpe.ID_PARTIDA_EJ and di.ID_PARTIDA_EJ = pej.ID_PARTIDA_EJ " _
                   & "and pej.FLEXIBLE = 1 and rpe.ID_PARTIDA_PS = ps.ID_PARTIDA_PS " _
                   & "and di.ESTATUS_DIST in (" & CTEstadoPEJ.enumEstatusPEJ.POR_DISTRIBUIR & "," & CTEstadoPEJ.enumEstatusPEJ.PARCIAL &") " _
                   & "and di.CANTIDAD_X_DISTRIB  > 0) INTER_FLEXIBLES, " _
                   & "(select count(*) from ( " _
                   & "select df.ID_PARTIDA_EJ, df.SEC_DISTRIB " _
                   & "from FL_OP_DISTRIB_FONDOINTER df, FL_REL_DISTRIB_PEJ dpe, FL_REL_PS_PEJ pspe " _
                   & "where df.ID_PARTIDA_EJ = dpe.ID_PARTIDA_EJ " _
                   & "and pspe.ID_PARTIDA_EJ = dpe.ID_PARTIDA_EJ " _
                   & "and dpe.ESTATUS_DIST not in (" & CTEstadoPEJ.enumEstatusPEJ.CANCELADA & "," & CTEstadoPEJ.enumEstatusPEJ.CERRADA &") " _
                   & "and df.ENVIADO_DECALOG = 0 " _
                   & "and pspe.ID_PARTIDA_PS = ps.ID_PARTIDA_PS " _
                   & "group by df.ID_PARTIDA_EJ, df.SEC_DISTRIB) pp) FOLIO_ENVIADO_DECALOG " _
                   & "from FL_REL_BSO_PS bso, FL_OP_PS ps, GEN_CAT_GEN ep, GEN_CAT_GEN tv " _
                   & "where bso.ID_PARTIDA_PS = ps.ID_PARTIDA_PS " _
                   & "and bso.CAT_ESTATUS_PARTIDA = ep.ID_CAT " _
                   & "and bso.ESTATUS_PARTIDA = ep.CLAVE " _
                   & "and ps.CAT_TVNG = tv.ID_CAT " _
                   & "and ps.TVNG = tv.CLAVE " _
                   & "and bso.ID_BSO = " & IdBSO _
                   & " order by ps.ID_PARTIDA_PS"


            dtPS = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtPS

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Shared Function ObtienePartidasAjuste(ByVal IdBSO As Integer) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtPA As New DataTable()

        Try
            sSql = "select pa.ID_PARTIDA_AJUSTE, pa.ID_PARTIDA_PS,ISNULL(pa.ID_PARTIDA_EJ, -1) AS ID_PARTIDA_EJ, pa.ID_TIPO_CANTIDAD, " _
                   & "pa.CANTIDAD_AJUS, pa.FEC_CREACION, pa.FEC_APLICACION, " _
                   & "pa.COMENTARIO, pa.ESTATUS, epa.VALOR ESTATUS_VALOR " _
                   & "from FL_OP_PAJ pa, FL_OP_PS ps, FL_REL_BSO_PS bso, GEN_CAT_GEN epa " _
                   & "where pa.ID_PARTIDA_PS = ps.ID_PARTIDA_PS " _
                   & "and ps.ID_PARTIDA_PS = bso.ID_PARTIDA_PS " _
                   & "and pa.CAT_ESTATUS_PAJ = epa.ID_CAT " _
                   & "and pa.ESTATUS = epa.CLAVE " _
                   & "and bso.ID_BSO = " & IdBSO _
                   & " order by pa.ID_PARTIDA_AJUSTE"


            dtPA = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtPA

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Shared Function ActualizaEstatusPA(ByVal idPartidaAjuste As Integer, ByVal estatus As Integer, ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer


        sSql = "update pa " _
             & "set pa.ESTATUS = " & estatus _
             & "from FL_OP_PAJ pa " _
             & "where pa.ID_PARTIDA_AJUSTE = " & idPartidaAjuste

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function ActualizaMontoPAAplicada(ByVal idPartidaAjuste As Integer, ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer


        sSql = "update ps " _
             & "set ps.CANTIDAD_ACTUAL = ps.CANTIDAD_ACTUAL - pa.CANTIDAD_AJUS " _
             & "from FL_OP_PAJ pa, FL_REL_BSO_PS ps " _
             & "where pa.ID_PARTIDA_PS = ps.ID_PARTIDA_PS " _
             & "and pa.ID_PARTIDA_AJUSTE = " & idPartidaAjuste

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function ActualizaEstatusPS(ByVal idPartida As Integer, ByVal estatus As Integer, ByVal comentario As String, ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer
        
        if comentario is Nothing Then
            comentario = ""
        End If

        sSql = "update ps " _
             & "set ps.ESTATUS_PARTIDA = " & estatus _
             & ", ps.COMENTARIO_CIERRE = '" & comentario & "'" _
             & " from FL_REL_BSO_PS ps " _
             & "where ps.ID_PARTIDA_PS = " & idPartida

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function CancelarPAsPorPartidaSol(ByVal idPartida As Integer, ByVal estatus As Integer, ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        sSql = "update ps " _
             & "set ps.ESTATUS_PARTIDA = " & estatus _
             & " from FL_REL_BSO_PS ps " _
             & "where ps.ID_PARTIDA_PS = " & idPartida

        sSql = sSql & "; update pa " _
             & "set pa.ESTATUS = " & CTPartidaAjuste.enumEstatusPAJ.CANCELADA &" " _
             & "from FL_OP_PAJ pa" _
             & " where pa.ID_PARTIDA_PS = " & idPartida

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function CrearBSO(ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

   
        sSql =  "insert into FL_OP_BSO values (" & BSO.BSOId & "," & BSO.BSOId & "," & BSO.EstatusBSOID & "," _
                & BSO.CatalogoEstatusID & ",'" & BSO.FecOperacion.ToString("yyyy-MM-dd hh:mm:ss") & "', getdate(), " & CServiciosMacrosSyBaseDB.IDUSUARIO & ")"
        
        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function CerrarBSO(ByVal fechaApertura As Date, ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer


        sSql =  "update bso set bso.ESTATUS_BSO = " & CTBSO.enumEstatusBSO.CERRADA & " from FL_OP_BSO bso where bso.ID_BSO = " & BSO.BSOId
        sSql =  sSql & "; update param set PARAMETRO_VALOR = '" & fechaApertura.ToString("yyyy-MM-dd") & "' from MMA_PARAMETROS param where ID = 57"
        sSql =  sSql & "; delete FL_NOTIFICACION"

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function CrearPS(ByRef pPs As CTPartidaSolicitud, ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer


        sSql = "insert into FL_OP_PS values (" & pPs.PartidaSolId & "," & pPs.PartidaSolId & "," & pPs.TvngId & "," & pPs.CatalogoTVNgID & ",'" _
                & pPs.FecSol.ToString("yyyy-MM-dd hh:mm:ss") & "','" & pPs.FecLiq.ToString("yyyy-MM-dd hh:mm:ss") _
                & "','" & pPs.FecPlazo.ToString("yyyy-MM-dd hh:mm:ss") & "','" & pPs.Comentarios _
                & "'," & pPs.TipoCantidadId & "," & pPs.CatalogoTipoCantidadID & "," & pPs.Plazo & "," & pPs.CantidadSol & ")"

        sSql = sSql & "; insert into FL_REL_BSO_PS values (" & BSO.BSOId & "," & pPs.PartidaSolId & "," & pPs.EstatusActualPS _
               & " ," & pPs.CatalogoEstatusID & "," & pPs.CantidadSol &",null)"

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function ModificarPS(ByRef pPs As CTPartidaSolicitud, ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        sSql = "update FL_OP_PS " _
               & "set TVNG = " & pPs.TvngId & "," _ 
               & " FEC_SOLICITUD = '" & pPs.FecSol.ToString("yyyy-MM-dd hh:mm:ss") & "'," _
               & " FEC_LIQUIDACION = '" & pPs.FecLiq.ToString("yyyy-MM-dd hh:mm:ss") & "'," _
               & " COMENTARIO = '" & pPs.Comentarios & "'," _
               & " TIPO_CANTIDAD = " & pPs.TipoCantidadId & "," _
               & " PLAZO = " & pPs.Plazo & "," _
               & " CANTIDAD_SOL = " & pPs.CantidadSol _
               & " where ID_PARTIDA_PS = " & pPs.PartidaSolId

        sSql = sSql & "; update FL_REL_BSO_PS " _
               & " set CANTIDAD_ACTUAL = " & pPs.CantidadSol _
               & " where ID_BSO = " & BSO.BSOId _
               & " and ID_PARTIDA_PS = " & pPs.PartidaSolId

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function CrearPAJ(ByRef pPaj As CTPartidaAjuste, ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer


        sSql = "insert into FL_OP_PAJ values (" & pPaj.PartidaAjusteID & "," & pPaj.PartidaSolicitudId & "," & pPaj.PartidaEJId _
                & "," & pPaj.TipoCantidadId &  "," & pPaj.CatalogoTipoCantidadID & ", getdate() ,'" & pPaj.FecAplicacion.ToString("yyyy-MM-dd hh:mm:ss") _
                & "','" &pPaj.Comentario & "'," & CTPartidaAjuste.enumEstatusPAJ.CREADA & "," & pPaj.CatalogoEstatusID _
                & "," & (pPaj.CantidadAjus * -1) & ")"

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function ModificarPAJ(ByRef pPaj As CTPartidaAjuste, ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        sSql = "update FL_OP_PAJ " _
               & " set CANTIDAD_AJUS = " & (pPaj.CantidadAjus * -1) _
               & " where ID_PARTIDA_AJUSTE = " & pPaj.PartidaAjusteID

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function CrearPSBSO(ByRef pPs As CTPartidaSolicitud, ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        
        sSql = "insert into FL_REL_BSO_PS values (" & BSO.BSOId & "," & pPs.PartidaSolId & "," & pPs.EstatusActualPS _
               & " ," & pPs.CatalogoEstatusID & "," & pPs.CantidadSol &", null)"
               
        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

     Public Shared Function ModificarPSBSO(ByRef pPs As CTPartidaSolicitud, ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        sSql = "update FL_REL_BSO_PS " _
               & " set CANTIDAD_ACTUAL = " & pPs.CantidadSol _
               & " where ID_BSO = " & BSO.BSOId _
               & " and ID_PARTIDA_PS = " & pPs.PartidaSolId
       
        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function RegistrarNotificacion(ByRef pId As Integer, ByRef pMensaje As String, ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer


        sSql =  "insert into FL_NOTIFICACION values (" &pId & ",'" & pMensaje & "', getdate())"
        
        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function ObtieneFolio(ByRef pCampo As String, ByRef pTabla As String) As Integer
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dt As New DataTable()

        Try
            sSql = "select case when count(*) = 0 then 1 else max(" & pCampo & ") + 1 end folio from " &  pTabla


            dt = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return -1
            End If

            Return dt.Rows(0).Item("folio")
            
        Catch ex As Exception
            Throw ex
        End Try
    End Function



  #Region "GESTION DE PARTIDAS DE EJECUCIÓN"

  
     Public Shared Function ObtenerPartidasEJPorBSOActual(ByVal IdBSO As Integer) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtPEJ As New DataTable()

        Try
                sSql =        " SELECT " 
                sSql = sSql & " PS_BSO.ID_PARTIDA_PS,  "
                sSql = sSql & " PS_BSO.ID_PARTIDA_EJ,  "
                sSql = sSql & " PS_BSO.PEJ_CONFIRMADA, "
                sSql = sSql & " isnull(PEJ.SEC_PEJ,0) AS SEC_PEJ, "
                sSql = sSql & " PEJ.HORA_INGRESO,  "
                sSql = sSql & " isnull(PEJ.HORA_EJECUCION,CURRENT_TIMESTAMP) AS HORA_EJECUCION, "
                sSql = sSql & " PEJ.TASA, "
                sSql = sSql & " PEJ.INTERMEDIARIO AS CVE_INTERMEDIARIO, "
                sSql = sSql & " CAT.VALOR   as NOM_INTERMEDIARIO, "
                sSql = sSql & " PARAMINTER.NIVEL_MAX_DISTRIB, "
                sSql = sSql & " PARAMINTER.NIVEL_MIN_DISTRIB, "
                sSql = sSql & " PEJ.CANTIDAD,  "
                sSql = sSql & " isnull(PEJ.MEDIO,'') AS MEDIO,  "
                sSql = sSql & " PEJ.FLEXIBLE,  "
                sSql = sSql & " isnull(PEJ.OBSERVACIONES,'') AS OBSERVACIONES,  "
                sSql = sSql & " DITRIBPEJ.CANTIDAD_DISTRIB,  "
                sSql = sSql & " DITRIBPEJ.CANTIDAD_X_DISTRIB, "
                sSql = sSql & " DITRIBPEJ.ESTATUS_DIST "                
                sSql = sSql & " FROM FL_OP_PEJ PEJ INNER JOIN FL_REL_DISTRIB_PEJ DITRIBPEJ ON (PEJ.ID_PARTIDA_EJ = DITRIBPEJ.ID_PARTIDA_EJ)  "
                sSql = sSql & " INNER JOIN ( "
                sSql = sSql & " SELECT ID_PARTIDA_EJ,ID_PARTIDA_PS,PEJ_CONFIRMADA FROM FL_REL_PS_PEJ WHERE ID_PARTIDA_PS IN ( "
                sSql = sSql & " SELECT ID_PARTIDA_PS FROM FL_OP_PS WHERE ID_PARTIDA_PS IN (SELECT ID_PARTIDA_PS FROM FL_REL_BSO_PS WHERE ID_BSO =" & IdBSO & "))) as PS_BSO ON PEJ.ID_PARTIDA_EJ = PS_BSO.ID_PARTIDA_EJ "
                sSql = sSql & " INNER JOIN GEN_CAT_GEN CAT ON CAT.ID_CAT = PEJ.CAT_INTERMEDIARIO AND CAT.CLAVE = PEJ.INTERMEDIARIO "
                sSql = sSql & " LEFT JOIN FL_PARAM_INTERMEDIARIO PARAMINTER ON PARAMiNTER.ID_INTERMEDIARIO = PEJ.INTERMEDIARIO "






            dtPEJ = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
               Dim errorFL =   New CError()
               errorFL.ATRAPADO = True
               errorFL.DESC_ERROR = sMsgError
               errorFL.CODCLASE = "DBFL02"
               Throw errorFL
            End If

            Return dtPEJ

        Catch ex As Exception
    
            Throw ex
        End Try



    End Function


     Public Shared Function CambiarEstatusPSEnviadaAEnProcesoDB(ByRef param As String, ByRef sMsgError As String,ByRef eventoBit As CSBitEvento ) As Boolean

         Dim db As New CBaseDeDatosSQLServer()
         Dim pm As New List(Of SqlParameter)
         Dim sSql As String                  
         Dim regAfec As Integer = 0
         
         Dim sql = New StringBuilder ()


         Dim lstDetError = New List(Of  CTBitErrorDet )


         Dim idPs() As String 
         idPs = param.Split (",")

         sMsgError  = ""

         sSql = "UPDATE FL_REL_BSO_PS SET ESTATUS_PARTIDA =" & CTPartidaSolicitud.enumEstatusPS.EN_PROCESO & "  WHERE ID_PARTIDA_PS IN (" &  param   & "); "
         
         sql.Append(SSQL)
         sql.AppendLine 
         For Each id As String In idPs 
             sql.Append(" INSERT INTO FL_REL_PS_PROC_EJECT (ID_PARTIDA_PS,HORA_INGRESO,HORA_EJECUCION) VALUES (")
             sql.Append (id)
             sql.Append (", CURRENT_TIMESTAMP, NULL); ")
             sql.AppendLine 

             lstDetError.Add(New CTBitErrorDet ("Id_PS",id.ToString ))

         Next

         If db.EjecutarComando(Sql.ToString (), pm,regAfec , sMsgError) = True Then            
            Return True
        Else
            Dim errorBit =  eventoBit.GuardarError (sMsgError)
            sMsgError = "Código del error: " & errorBit.IdError & "-" & errorBit.IdRegEvento  
            errorBit.GuardarDetalles (lstDetError ) 
            Return false       
        End If

     End Function

     Public Shared Function CambiarEstatusPSEnProcesoALiberadaDB(ByRef param As String, ByRef sMsgError As String) As Boolean

         Dim db As New CBaseDeDatosSQLServer()
         Dim pm As New List(Of SqlParameter)
         Dim sSql As String                  
         Dim regAfec As Integer = 0

         sMsgError  = ""



         Dim sql = New StringBuilder ()
         Dim idPs() As String 
         idPs = param.Split (",")

         sSql = "UPDATE FL_REL_BSO_PS SET ESTATUS_PARTIDA =" & CTPartidaSolicitud.enumEstatusPS.LIBERADA  & " WHERE ID_PARTIDA_PS IN (" &  param   & ")"


         sql.Append(SSQL)
         sql.AppendLine 
         For Each id As String In idPs 
             sql.Append(" UPDATE FL_REL_PS_PROC_EJECT SET HORA_EJECUCION = CURRENT_TIMESTAMP WHERE ID_PARTIDA_PS = ")             
             sql.Append (id)
             sql.Append ("; ")
             sql.AppendLine 
         Next


   


         If db.EjecutarComando(sql.ToString() , pm,regAfec , sMsgError) = True Then            
            Return True
        Else
            Return false       
        End If

     End Function

     Public Shared Function CancelarPSDesdeEjecucionDB(ByVal idPs As Long, ByVal observacion As String, ByRef sMsgError As String) As Boolean

      Dim db      As New CBaseDeDatosSQLServer()
      Dim pm      As New List(Of SqlParameter)         
      Dim regAfec As Integer = 0     
      

                       
      Dim sql        = New  StringBuilder ("")


      
      sql.Append (" UPDATE FL_REL_PS_PROC_EJECT SET OBSERVA_CANCELACION =  '")
      sql.Append (observacion)
      sql.Append ("', HORA_CANCELA = CURRENT_TIMESTAMP WHERE ID_PARTIDA_PS = ")
      sql.Append (idPs)
      sql.Append ("; ")
      sql.AppendLine
      

      sql.Append (" UPDATE FL_REL_BSO_PS SET ESTATUS_PARTIDA= ")
      sql.Append (CTPartidaSolicitud.enumEstatusPS.CANCELADA)
      sql.Append (" WHERE ID_PARTIDA_PS =  ")
      sql.Append (idPs)
      sql.Append ("; ")
      sql.AppendLine


      If db.EjecutarComando(Sql.ToString() , pm,regAfec , sMsgError) = True Then            
            Return True
      Else
            Return false       
      End If

     End Function

      Public Shared Function ObtenerPSProcEjectDB(ByVal filtroIdPS As String) As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtPSProcEjectDB As New DataTable()

        Try
                sSql =        " SELECT " 
                sSql = sSql & " ID_PARTIDA_PS, "
                sSql = sSql & " HORA_INGRESO, "
                sSql = sSql & " HORA_EJECUCION, "
                sSql = sSql & " isNULL( OBSERVA_CANCELACION, '') as OBSERVA_CANCELACION , "
                sSql = sSql & " isNULL( HORA_CANCELA,'') as  HORA_CANCELA "
                sSql = sSql & " FROM FL_REL_PS_PROC_EJECT "
                sSql = sSql & " WHERE "
                sSql = sSql & " ID_PARTIDA_PS IN (" & filtroIdPS & ");"
                        

            dtPSProcEjectDB = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then               
              Return Nothing 
            End If

            Return dtPSProcEjectDB

        Catch ex As Exception    
            Return Nothing 
        End Try

     End Function

    
     Public Shared Function ObtenerCatalogoInterParamDB() As DataTable
          Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtPEJ As New DataTable()

        Try
                sSql =        " SELECT " 
                sSql = sSql & " PINTER.ID_INTERMEDIARIO,"
                sSql = sSql & " CATINTER.VALOR AS NOMBRE, "
                sSql = sSql & " PINTER.NIVEL_MAX_DISTRIB, "
                sSql = sSql & " PINTER.NIVEL_MIN_DISTRIB, "
                sSql = sSql & " PINTER.SELECCIONADO  "
                sSql = sSql & " from FL_PARAM_INTERMEDIARIO PINTER inner JOIN GEN_CAT_GEN CATINTER ON (PINTER.ID_INTERMEDIARIO = CATINTER.CLAVE)  "
                sSql = sSql & " WHERE CATINTER.ID_CAT =  " & __CLAVE_CAT_INTERMEDIARIO__

            dtPEJ = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
               Dim errorFL =   New CError()
               errorFL.ATRAPADO = True
               errorFL.DESC_ERROR = sMsgError
               errorFL.CODCLASE = "DBFL02"
               Throw errorFL
            End If

            Return dtPEJ

        Catch ex As Exception
    
            Throw ex
        End Try
     End Function


      Public Shared Function ObtenerCatalogoInterParamDB(ByRef idTVNG As Integer ) As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtPEJ As New DataTable()

        Try
                sSql =        " SELECT " 
                sSql = sSql & " PINTER.ID_INTERMEDIARIO,"
                sSql = sSql & " CATINTER.VALOR AS NOMBRE, "
                sSql = sSql & " PINTER.NIVEL_MAX_DISTRIB, "
                sSql = sSql & " PINTER.NIVEL_MIN_DISTRIB, "
                sSql = sSql & " PINTER.SELECCIONADO"
                sSql = sSql & " from FL_PARAM_INTERMEDIARIO PINTER inner JOIN GEN_CAT_GEN CATINTER ON (PINTER.ID_INTERMEDIARIO = CATINTER.CLAVE) INNER JOIN  "
                sSql = sSql & "   (SELECT DISTINCT(ID_INTERMEDIARIO)  FROM FL_REL_FONDO_INTERMEDIARIO WHERE FONDO IN  "
                sSql = sSql & "   (SELECT FONDO  FROM FL_REL_FONDO_TVNG WHERE ID_TVNG = " & idTVNG
                sSql = sSql & " )) AS INTER ON (PINTER.ID_INTERMEDIARIO = INTER.ID_INTERMEDIARIO) "
                sSql = sSql & " WHERE CATINTER.ID_CAT =  " & __CLAVE_CAT_INTERMEDIARIO__

            dtPEJ = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
               Dim errorFL =   New CError()
               errorFL.ATRAPADO = True
               errorFL.DESC_ERROR = sMsgError
               errorFL.CODCLASE = "DBFL02"
               Throw errorFL
            End If

            Return dtPEJ

        Catch ex As Exception
    
            Throw ex
        End Try
     End Function


     Public Shared sub GuardarInfoPEJEnDB(ByRef pLstInsert As IEnumerable, _ 
                                               ByRef pLstUpdate As  IEnumerable, _
                                               ByRef pLstIdDelete As List(Of Long), _
                                               ByRef psSel  As CTPartidaSolicitud, _
                                               ByRef lstFallidas As List(Of StringBuilder ), _
                                               Optional ByRef eventoBit As CSBitEvento = Nothing  ) 


      'Comentario: posible migración a Store Procedure
       Dim sMsgError As String  = ""       
       Dim  falla   As StringBuilder 
       Dim  bitError As CSBitError = Nothing 
       
       Try

       

        For Each alta As SubPej  In  pLstInsert             
          if InsertarPEJBD(alta,psSel.PartidaSolId, sMsgError) = False then 
               falla = New StringBuilder ("Error en el Alta: ")             
               falla.Append(alta.Intermediario.NombreIntermediario)
               falla.Append(", ")
               falla.Append(alta.Tasa )
               falla.Append(", ")
               falla.Append(alta.CantidadAsigInterm  )
               falla.Append("; para la PS: " )
               falla.Append(psSel.PartidaSolId)
               falla.Append(": ")
               bitError = eventoBit.GuardarError (falla.ToString ())
               
               falla.Append(sMsgError)                              
               bitError.GuardarDetalle ("InsertarPEJBD_Err",sMsgError)
               

               sMsgError = ""
               lstFallidas.Add(falla)

          End If           
        Next             
     
     
        For Each modificacion As SubPej  In  pLstUpdate  
             if UpdatePEJBD(modificacion, sMsgError) = False then 
               falla = New StringBuilder ("Error en la Actualización: ID")             
               falla.Append(modificacion.PartidaEJId)
               falla.Append(", ")
               falla.Append(modificacion.Intermediario.NombreIntermediario)
               falla.Append(", ")
               falla.Append(modificacion.Tasa )
               falla.Append(", ")
               falla.Append(modificacion.CantidadAsigInterm  )
               falla.Append("; para la PS: " )
               falla.Append(psSel.PartidaSolId)
               falla.Append(": ")
               bitError = eventoBit.GuardarError  (falla.ToString ())

               falla.Append(sMsgError)
               bitError.GuardarDetalle  ("UpdatePEJBD_Err",sMsgError)

               sMsgError = ""
               lstFallidas.Add(falla)
            End If                           



            
        Next             
     

        For Each eliminacion As Long  In  pLstIdDelete  
            
             if DeletePEJBD(eliminacion, sMsgError) = False then 
               falla = New StringBuilder ("Error en la Eliminación: ID")             
               falla.Append(eliminacion)               
               falla.Append("; para la PS: " )
               falla.Append(psSel.PartidaSolId)
               falla.Append(": ")
               bitError =  eventoBit.GuardarError  (falla.ToString ())

               falla.Append(sMsgError)
               bitError.GuardarDetalle("DeletePEJBD_Err",sMsgError)

               sMsgError = ""
               lstFallidas.Add(falla)
          End If             

        Next             


     Catch ex As Exception                       
         Throw ex
     End Try

                          
     End Sub 
    

     Private Shared Function InsertarPEJBD (ByRef pSubPej As SubPej, ByVal idPS As Long,  ByRef sMsgError As String) As Boolean
         Dim db      As New CBaseDeDatosSQLServer()
         Dim pm      As New List(Of SqlParameter)         
         Dim regAfec As Integer = 0     
     
                
          Dim idPEJ      As Long          
          Dim sql        = New  StringBuilder ("")


            idPEJ = ObtenerFolioPEJ()

            If idPEJ = -1 then 
               sMsgError = "No se pudo generar el folio PEJ"
               Return False 
            End If


               sql.Append ("INSERT INTO FL_OP_PEJ ")
               sql.Append ( "(ID_PARTIDA_EJ," )    
               sql.Append ( "SEC_PEJ,")               
               sql.Append ( "HORA_INGRESO,")
               sql.Append ( "HORA_EJECUCION,")
               sql.Append ( "TASA,")
               sql.Append ( "CAT_INTERMEDIARIO, ")
               sql.Append ( "INTERMEDIARIO,")
               sql.Append ( "CANTIDAD, ")
               sql.Append ( "MEDIO,")
               sql.Append ( "FLEXIBLE, ")
               sql.Append ( "OBSERVACIONES)")
               sql.Append ( " VALUES ")
               sql.Append ( " ( "  )
               sql.Append (  idPEJ )
               sql.Append (  ",")
               sql.Append ( " 0, ")
               sql.Append ( " CURRENT_TIMESTAMP, ")
               sql.Append ( " CURRENT_TIMESTAMP, ")
               sql.Append ( pSubPej.Tasa )
               sql.Append ( ", ")
               sql.Append ( __CLAVE_CAT_INTERMEDIARIO__ )
               sql.Append ( ",")
               sql.Append ( pSubPej.Intermediario.IdIntermediario)
               sql.Append ( ",")
               sql.Append ( pSubPej.CantidadAsigInterm)
               sql.Append ( ",'")
               sql.Append ( pSubPej.Medio)
               sql.Append ( "',")
               sql.Append (IIf(pSubPej.Flexible,1,0))
               sql.Append ( ",'")
               sql.Append ( pSubPej.Observaciones)
               sql.Append ("'); ")
               
               sql.AppendLine 

                                           
               sql.Append(" INSERT INTO FL_REL_DISTRIB_PEJ ( " )
               sql.Append(" ID_PARTIDA_EJ, ")
               sql.Append(" CANTIDAD_X_DISTRIB, ")
               sql.Append(" CANTIDAD_DISTRIB, ")
               sql.Append(" ESTATUS_DIST, ")
               sql.Append(" CAT_ESTATUS_DIST )")
               sql.Append(" VALUES (")
               sql.Append(idPEJ)
               sql.Append(",")
               sql.Append (pSubPej.CantidadAsigInterm)
               sql.Append(",")
               sql.Append("0,")
               sql.Append("1,") 'ESTATUS: "POR DISTRIBUIR"
               sql.Append(__CLAVE_CAT_ESTATUS_DISTRIB_PEJ__)
               sql.Append(");")     
               

               sql.AppendLine 

               sql.Append(" INSERT INTO FL_REL_PS_PEJ ( " )
               sql.Append(" ID_PARTIDA_EJ, ")
               sql.Append(" ID_PARTIDA_PS, ")
               sql.Append(" PEJ_CONFIRMADA)")
               sql.Append(" VALUES (")
               sql.Append(idPEJ)
               sql.Append(",")
               sql.Append(idPS )
               sql.Append(",")
               sql.Append("0); ")


               
                If db.EjecutarComando(Sql.ToString() , pm,regAfec , sMsgError) = True Then            
                   Return True
                Else
                   Return false       
                End If

     End Function

     Private Shared Function UpdatePEJBD(ByRef pSubPej As SubPej,  ByRef sMsgError As String) As Boolean 

         Dim db      As New CBaseDeDatosSQLServer()
         Dim pm      As New List(Of SqlParameter)         
         Dim regAfec As Integer = 0     
                          
         Dim sql        = New  StringBuilder ("")
         
         sql.Append (" UPDATE FL_OP_PEJ SET ")
         sql.Append (" TASA = ")
         sql.Append (pSubPej.Tasa)         
         sql.Append (",INTERMEDIARIO = ")
         sql.Append (pSubPej.Intermediario.IdIntermediario)
         sql.Append (",CANTIDAD = ")
         sql.Append (pSubPej.CantidadAsigInterm)
         sql.Append (",MEDIO = '")
         sql.Append (pSubPej.Medio)
         sql.Append ("',FLEXIBLE = ")
         sql.Append (IIf(pSubPej.Flexible,1,0))
         sql.Append (",OBSERVACIONES = '")
         sql.Append (pSubPej.Observaciones)
         sql.Append ("' WHERE ID_PARTIDA_EJ = ")
         sql.Append (pSubPej.PartidaEJId)
         sql.Append (";")

         sql.AppendLine  

         sql.Append (" UPDATE  FL_REL_DISTRIB_PEJ SET CANTIDAD_X_DISTRIB = ")
         sql.Append (pSubPej.CantidadAsigInterm)
         sql.Append (" WHERE ID_PARTIDA_EJ =  ")
         sql.Append (pSubPej.PartidaEJId)
         sql.Append (";")





         If db.EjecutarComando(Sql.ToString() , pm,regAfec , sMsgError) = True Then            
            Return True
         Else
            Return false       
         End If

     End Function

     Private Shared Function DeletePEJBD(ByRef idPEJ As long,  ByRef sMsgError As String) As Boolean 

         Dim db      As New CBaseDeDatosSQLServer()
         Dim pm      As New List(Of SqlParameter)         
         Dim regAfec As Integer = 0     
                          
         Dim sql        = New  StringBuilder ("")
         
         sql.Append ("DELETE FROM FL_REL_DISTRIB_PEJ WHERE ID_PARTIDA_EJ = ")
         sql.Append (idPEJ)
         sql.Append ("; ")
         
         sql.AppendLine 

         sql.Append ("DELETE FROM FL_REL_PS_PEJ WHERE ID_PARTIDA_EJ = ")
         sql.Append (idPEJ)
         sql.Append ("; ")
         
         sql.AppendLine 

         sql.Append ("DELETE FROM FL_OP_PEJ WHERE ID_PARTIDA_EJ = ")
         sql.Append (idPEJ)
         sql.Append ("; ")
         
         If db.EjecutarComando(Sql.ToString() , pm,regAfec , sMsgError) = True Then            
            Return True
         Else
            Return false       
         End If

     End Function




     Public Shared Function ActualizarFechaPeriodoEj(ByVal sFecha As String, ByRef sMsgError As String ) As Boolean 

         Dim db      As New CBaseDeDatosSQLServer()
         Dim pm      As New List(Of SqlParameter)         
         Dim regAfec As Integer = 0       

         Dim sSql    As String 

         sSql = " UPDATE MMA_PARAMETROS SET PARAMETRO_VALOR = '" & sFecha & "' WHERE ID = " & __CLAVE_CAT_FECHA_PERIODO_EJ__


          If db.EjecutarComando(Ssql , pm,regAfec , sMsgError) = True Then            
            Return True
         Else
            Return false       
         End If
         
     End Function

     Public Shared Function ObtenerSimulacionBaseA(ByVal sFechaPeriodoEJ As String ) As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim Sql  = New StringBuilder ("")
        Dim sMsgError As String = ""

        Dim dtPEJ As New DataTable()

        Try
             
            Sql.Append ("SELECT AUXINTER.INTERMEDIARIO, sum(AUXINTER.CANTIDAD) AS CANTIDAD FROM ")
            Sql.Append ("( ")
                
            Sql.Append ("SELECT ")
            Sql.Append ("A.INTERMEDIARIO, ")
            Sql.Append ("SUM((B.CANTIDAD_DISTRIB + B.CANTIDAD_X_DISTRIB)) AS CANTIDAD    ")
            Sql.Append ("FROM FL_OP_PEJ A ")
            Sql.Append ("INNER JOIN FL_REL_DISTRIB_PEJ B ON (A.ID_PARTIDA_EJ = B.ID_PARTIDA_EJ) ")
            Sql.Append ("INNER JOIN  FL_REL_PS_PEJ C ON A.ID_PARTIDA_EJ = C.ID_PARTIDA_EJ  ")
            Sql.Append ("INNER JOIN  FL_REL_BSO_PS D ON D.ID_PARTIDA_PS = C.ID_PARTIDA_PS  ")
            Sql.Append ("WHERE ")
            Sql.Append ("D.ESTATUS_PARTIDA IN ( ")
            Sql.Append (CTPartidaSolicitud.enumEstatusPS.EN_PROCESO  & "," & CTPartidaSolicitud.enumEstatusPS.LIBERADA & "," & CTPartidaSolicitud.enumEstatusPS.EN_DISTRIBUCION & "," & CTPartidaSolicitud.enumEstatusPS.CERRADA)
            Sql.Append (") AND A.HORA_EJECUCION >= '")
            Sql.Append (sFechaPeriodoEJ)
            Sql.Append ("' GROUP BY A.INTERMEDIARIO ")
            
            Sql.Append (" union all ")


            Sql.Append (" SELECT A.INTERMEDIARIO, SUM((B.CANTIDAD_DISTRIB)) AS CANTIDAD   ")
            Sql.Append (" FROM FL_OP_PEJ A  ")
            Sql.Append (" INNER JOIN FL_REL_DISTRIB_PEJ B ON (A.ID_PARTIDA_EJ = B.ID_PARTIDA_EJ)  ")
            Sql.Append (" INNER JOIN  FL_REL_PS_PEJ C ON A.ID_PARTIDA_EJ = C.ID_PARTIDA_EJ  ")
            Sql.Append (" INNER JOIN  FL_REL_BSO_PS D ON D.ID_PARTIDA_PS = C.ID_PARTIDA_PS   ")
            Sql.Append (" WHERE  ")
            Sql.Append (" D.ESTATUS_PARTIDA =  ")
            Sql.Append (CTPartidaSolicitud.enumEstatusPS.CERRADA_INCOMPLETA)
            Sql.Append (" AND A.HORA_EJECUCION >= '")
            Sql.Append (sFechaPeriodoEJ)
            Sql.Append ("' GROUP BY A.INTERMEDIARIO ")
            Sql.Append (" ) as AUXINTER ")
            Sql.Append (" GROUP BY AUXINTER.INTERMEDIARIO ")
            Sql.Append (" ORDER BY 1,2 ASC ")

      

            dtPEJ = db.EjecutarConsulta(Sql.ToString (), pm, sMsgError)


            


            If Not sMsgError.Equals("") Then
               Dim errorFL =   New CError()
               errorFL.ATRAPADO = True
               errorFL.DESC_ERROR = sMsgError
               errorFL.CODCLASE = "DBFL02"
               Throw errorFL
            End If

            Return dtPEJ

        Catch ex As Exception
    
            Throw ex
        End Try
     End Function
     

     Public Shared Function ObtenerFechaPeriodoEJ() As String 
 
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFecha As New DataTable()

        Try
                sSql = "  SELECT PARAMETRO_VALOR  FROM MMA_PARAMETROS where ID =  " & __CLAVE_CAT_FECHA_PERIODO_EJ__                              
                dtFecha = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return Nothing
            End If


            If dtFecha.Rows.Count  = 1 THEN 
                 Return dtFecha.Rows(0)("PARAMETRO_VALOR")
            Else
                 Return Nothing 
            End If

        Catch ex As Exception    
            Return Nothing 
        End Try     
      


     End Function

     Public Shared Function ObtenerMontoTotalPSEnperiodo(ByVal sFecha As String, ByRef sMsgError As String) As Decimal 
        
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        

        Dim dtMonto As New DataTable()

          Try



                       sSql = " SELECT SUM(MONTO) AS MONTO FROM  "
                sSql = sSql & " ( "
                sSql = sSql & " SELECT A.CANTIDAD_SOL AS MONTO FROM FL_OP_PS A "
                sSql = sSql & " INNER JOIN  FL_REL_BSO_PS B ON A.ID_PARTIDA_PS = B.ID_PARTIDA_PS   "
                sSql = sSql & " WHERE B.ESTATUS_PARTIDA IN (" & CTPartidaSolicitud.enumEstatusPS.EN_PROCESO & "," &   CTPartidaSolicitud.enumEstatusPS.LIBERADA & "," & CTPartidaSolicitud.enumEstatusPS.EN_DISTRIBUCION & ","  & CTPartidaSolicitud.enumEstatusPS.CERRADA & "," & CTPartidaSolicitud.enumEstatusPS.CERRADA_INCOMPLETA & ")"
                sSql = sSql & " AND A.FEC_SOLICITUD  >= '" & sFecha & "'"
                sSql = sSql & " UNION ALL "
                sSql = sSql & " SELECT  -B.CANTIDAD_ACTUAL AS MONTO  FROM FL_OP_PS A "
                sSql = sSql & " INNER JOIN  FL_REL_BSO_PS B ON A.ID_PARTIDA_PS = B.ID_PARTIDA_PS   "
                sSql = sSql & " WHERE B.ESTATUS_PARTIDA IN (" & CTPartidaSolicitud.enumEstatusPS.CERRADA_INCOMPLETA & ") "
                sSql = sSql & " AND A.FEC_SOLICITUD  >= '" & sFecha & "'"
                sSql = sSql & " UNION ALL "
                sSql = sSql & " SELECT SUM(PAJ.CANTIDAD_AJUS ) AS MONTO FROM FL_OP_PAJ PAJ "
                sSql = sSql & " WHERE PAJ.ID_PARTIDA_PS IN ( "
                sSql = sSql & " SELECT A.ID_PARTIDA_PS FROM FL_OP_PS A "
                sSql = sSql & " INNER JOIN  FL_REL_BSO_PS B ON A.ID_PARTIDA_PS = B.ID_PARTIDA_PS  "
                sSql = sSql & " WHERE B.ESTATUS_PARTIDA IN (" & CTPartidaSolicitud.enumEstatusPS.EN_PROCESO & "," &   CTPartidaSolicitud.enumEstatusPS.LIBERADA & "," & CTPartidaSolicitud.enumEstatusPS.EN_DISTRIBUCION & ","  & CTPartidaSolicitud.enumEstatusPS.CERRADA & "," & CTPartidaSolicitud.enumEstatusPS.CERRADA_INCOMPLETA & ")"
                sSql = sSql & " AND A.FEC_SOLICITUD  >= '" & sFecha & "')"
                sSql = sSql & " ) AS AUX"
                

                dtMonto = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return -1
            End If


            If dtMonto.Rows.Count  = 1 THEN 
                 Return  Decimal.Round ( Convert.ToDecimal (dtMonto.Rows(0)("MONTO")),2)
            Else
                 sMsgError = "Multiples montos en la operación"
                 Return -1
            End If

        Catch ex As Exception    
            Return -1 
        End Try     
      

     End Function


    Public Shared Function AplicarPartidaAjusteDB(ByRef  idPS As Long, ByRef   idPEJ As Long, ByRef  idPAJ As Long, ByRef sMsgError As String) As Boolean

      Dim db      As New CBaseDeDatosSQLServer()
      Dim pm      As New List(Of SqlParameter)         
      Dim regAfec As Integer = 0     
                       
      Dim sql        = New  StringBuilder ("")


      '1) Actualizar la cantidad por distribuir, se resta la cantidad de ajuste
      sql.Append (" UPDATE FL_REL_DISTRIB_PEJ SET CANTIDAD_X_DISTRIB = CANTIDAD_X_DISTRIB + (SELECT CANTIDAD_AJUS  FROM FL_OP_PAJ WHERE ID_PARTIDA_AJUSTE = ")
      sql.Append (idPAJ)
      sql.Append (" ) WHERE ID_PARTIDA_EJ = ")
      sql.Append (idPEJ)
      sql.Append (";")


      '2) Actualizar la cantidad actual de la PS
      sql.Append (" UPDATE FL_REL_BSO_PS SET CANTIDAD_ACTUAL = CANTIDAD_ACTUAL + (SELECT CANTIDAD_AJUS  FROM FL_OP_PAJ WHERE ID_PARTIDA_AJUSTE = ")
      sql.Append (idPAJ)
      sql.Append (" ) WHERE ID_PARTIDA_PS = ")
      sql.Append (idPS)
      sql.Append ("; ")


      '3) Actualizar el estatus de la Partida de Ajuste 
      sql.Append (" UPDATE FL_OP_PAJ SET ESTATUS = ")
      sql.Append (CTPartidaAjuste.enumEstatusPAJ.APLICADA)
      sql.Append (", FEC_APLICACION = CURRENT_TIMESTAMP ")
      sql.Append (" WHERE ID_PARTIDA_AJUSTE = ")
      sql.Append (idPAJ)


      If db.EjecutarComando(Sql.ToString() , pm,regAfec , sMsgError) = True Then            
            Return True
      Else
            Return false       
      End If


    End Function
     

    Public Shared Function ObtenerIntermediariosUtilizadosDB(ByRef idBSO As long) As DataTable 

        Dim db        As New CBaseDeDatosSQLServer()
        Dim pm        As New List(Of SqlParameter)
        Dim sSql      As String
        Dim sMsgError As String = ""
        

        Dim dtIdIntermediario As New DataTable()

          Try
                       sSql = " SELECT DISTINCT( INTERMEDIARIO) FROM FL_OP_PEJ WHERE ID_PARTIDA_EJ IN (  "                                                    
                sSql = sSql & " SELECT ID_PARTIDA_EJ  FROM FL_REL_PS_PEJ WHERE ID_PARTIDA_PS IN( "
                sSql = sSql & " SELECT ID_PARTIDA_PS  FROM FL_REL_BSO_PS A INNER JOIN FL_OP_BSO B ON (A.ID_BSO = B.ID_BSO AND B.ESTATUS_BSO  =  "
                sSql = sSql & idBSO
                sSql = sSql & " )))"


                dtIdIntermediario = db.EjecutarConsulta(sSql, pm, sMsgError)

                If Not sMsgError.Equals("") Then
                    Return Nothing 
                End If


                Return dtIdIntermediario
     
        Catch ex As Exception    
            Return Nothing 
        End Try     
     

    End Function



     Public Shared Function ActualizarParamInterDB(ByRef lstInterParam As List(Of CTParamIntermediario),ByRef sMsgError As String, Optional ByRef  eventoBit As CSBitEvento = Nothing ) As Boolean 

      Dim db        As New CBaseDeDatosSQLServer()
      Dim pm        As New List(Of SqlParameter)         
      Dim regAfec   As Integer = 0     
      Dim sSql      As String = ""
            
                       
      Dim sqlBl        = New  StringBuilder ("")
      
      Dim lstEvenBitDet = New List(Of CTBitEventoDet )



      For Each ParamInter As CTParamIntermediario In lstInterParam 
          sSql =   "UPDATE FL_PARAM_INTERMEDIARIO SET SELECCIONADO =" &  Convert.ToInt16 (ParamInter.EsSeleccionado).ToString & " , NIVEL_MAX_DISTRIB =" &  ParamInter.NivelMaxDistrib   & " , NIVEL_MIN_DISTRIB = " & ParamInter.NivelMinDistrib   & " WHERE ID_INTERMEDIARIO = " & ParamInter.IdIntermediario & "; " 
          sqlBl.Append(sSql)    
          sqlBl.AppendLine      
                    
          lstEvenBitDet.Add (New CTBitEventoDet ("DETALLE INTERMEDIARIO (" & ParamInter.IdIntermediario.ToString () & "-" & ParamInter.NombreIntermediario  & ")",  "SEL:" & ParamInter.EsSeleccionado.ToString & "; NIVEL_MAX: " & ParamInter.NivelMaxDistrib & "; NIVEL_MIN: " & ParamInter.NivelMinDistrib ))  
                    
      Next


      eventoBit.GuardarDetalles (lstEvenBitDet )


      If db.EjecutarComando(sqlBl.ToString() , pm,regAfec , sMsgError) = True Then            
            Return True
      Else
            Return false       
      End If




    End Function


     Private Shared  function ObtenerFolioPEJ() As Long 
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFolio As New DataTable()

        Try
                sSql = " SELECT (ISNULL(MAX(ID_PARTIDA_EJ),0)+ 1) as folio FROM FL_OP_PEJ "                              
                dtFolio = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return -1
            End If


            If dtFolio.Rows(0)("FOLIO") > 0 THEN 
                 Return dtFolio.Rows(0)("FOLIO")
            Else
                 Return -1
            End If

        Catch ex As Exception    
            Return -1
        End Try     
      

     End Function
     
     
     Public Shared  Function ObtenerMontoTolerancia() As Double 

       
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""
        

        Dim dtMonto As New DataTable()

          Try
                
                
                sSql = " SELECT PARAMETRO_VALOR  FROM MMA_PARAMETROS WHERE ID = 59 "                                             
                dtMonto = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return 0
            End If


            If dtMonto.Rows.Count  = 1 THEN 
                 Return  Convert.ToDecimal (dtMonto.Rows(0)("PARAMETRO_VALOR"))
            Else
                 sMsgError = "Multiples valores en la operación"
                 Return 0
            End If

        Catch ex As Exception    
            Return 0
        End Try     
      



     End Function


     
    
 #End Region

  #Region "RESUMEN DISTRIBUCION FONDO - INTERMEDIARIO"
    Public Shared Function ObtieneIntermediariosTasa(ByVal idPS As Integer) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtInter As New DataTable()

        Try
            sSql = "select df.ID_PARTIDA_EJ, df.SEC_DISTRIB, pspe.ID_PARTIDA_PS," _
                 & " (convert(varchar, df.SEC_DISTRIB) + ' - ' +  di.VALOR + ' - Tasa : ' + replace(convert(varchar, pe.TASA),'.',',')) INTERMEDIARIO" _
                 & " from FL_OP_DISTRIB_FONDOINTER df, FL_REL_DISTRIB_PEJ dpe, FL_REL_PS_PEJ pspe," _
                 & " FL_OP_PEJ pe, GEN_CAT_GEN di" _
                 & " where df.ID_PARTIDA_EJ = dpe.ID_PARTIDA_EJ" _
                 & " and pspe.ID_PARTIDA_EJ = dpe.ID_PARTIDA_EJ" _
                 & " and pe.ID_PARTIDA_EJ = dpe.ID_PARTIDA_EJ" _
                 & " and di.ID_CAT = pe.CAT_INTERMEDIARIO" _     
                 & " and di.CLAVE = pe.INTERMEDIARIO" _  
                 & " and dpe.ESTATUS_DIST in (" & CTEstadoPEJ.enumEstatusPEJ.COMPLETA & "," & CTEstadoPEJ.enumEstatusPEJ.PARCIAL & ")" _
                 & " and df.ENVIADO_DECALOG = 0 " _
                 & " and pspe.ID_PARTIDA_PS = " & idPS.ToString() _  
                 & " order by df.ID_PARTIDA_EJ, df.SEC_DISTRIB"
            
            dtInter = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtInter

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Shared Function ObtieneDistribucion(ByRef dtDatos As DataTable, ByVal idPs As Long) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sMsgError As String = ""
        Dim query = New StringBuilder("")
        Dim dtDistribucion As New DataTable()

        Try
            
            If dtDatos.Rows.Count > 0 Then
                query.Append("select cf.FONDO")
                For Each dt As DataRow In dtDatos.Rows

                    query.Append(", convert(varchar,convert(Money, (isnull((select cff.CANTIDAD")
                    query.Append(" from FL_OP_CANTIDADXFONDO cff")
                    query.Append(" where cff.ID_PARTIDA_EJ = " + Convert.ToString(dt("ID_PARTIDA_EJ")))
                    query.Append(" and cff.SEC_DISTRIB = " + Convert.ToString(dt("SEC_DISTRIB")))
                    query.Append(" and cff.ID_PARTIDA_EJ = cf.ID_PARTIDA_EJ")
                    query.Append(" and cff.SEC_DISTRIB = cf.SEC_DISTRIB")    
                    query.Append(" and cff.FONDO = cf.FONDO), 0))),1) " + "'" + Convert.ToString(dt("INTERMEDIARIO"))  +"'")
                   
                Next
                query.Append(" , cf.ID_PARTIDA_EJ, cf.SEC_DISTRIB")
                query.Append(" from FL_OP_PEJ pe, FL_REL_DISTRIB_PEJ dp, FL_OP_CANTIDADXFONDO cf, FL_REL_PS_PEJ pspe,")
                query.Append(" FL_OP_DISTRIB_FONDOINTER df")
                query.Append(" where pe.ID_PARTIDA_EJ = dp.ID_PARTIDA_EJ")
                query.Append(" and cf.ID_PARTIDA_EJ = pe.ID_PARTIDA_EJ")
                query.Append(" and pspe.ID_PARTIDA_EJ = cf.ID_PARTIDA_EJ")
                query.Append(" and df.ID_PARTIDA_EJ = pspe.ID_PARTIDA_EJ")
                query.Append(" and df.SEC_DISTRIB = cf.SEC_DISTRIB")
                query.Append(" and dp.ESTATUS_DIST in (" & CTEstadoPEJ.enumEstatusPEJ.COMPLETA & "," & CTEstadoPEJ.enumEstatusPEJ.PARCIAL & ")")
                query.Append(" and cf.CANTIDAD <> 0")
                'query.Append(" and df.ESTATUS_RECUP_FOLIO <> " & CTDistribucionGL.enumEstatusFolioRecupDecalog.RECUPERADO) 
                query.Append(" and df.ENVIADO_DECALOG = 0")
                query.Append(" and pspe.ID_PARTIDA_PS = " & idPs.ToString())
                query.Append(" order by cf.ID_PARTIDA_EJ, cf.SEC_DISTRIB")
            End If


            dtDistribucion = db.EjecutarConsulta(query.ToString(), pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtDistribucion

        Catch ex As Exception
            Throw ex
        End Try

    End Function
#End Region

  #Region "Distribución de la Liquidez"
  
        Public Shared Function ObtenerMontosInterDistribDB(ByVal idPEJ As Long) As DataTable 

        
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""
        

        Dim dtMontos As New DataTable()

          Try
                
                
                       sSql = " SELECT (CANTIDAD_X_DISTRIB + CANTIDAD_DISTRIB) AS TOTAL, CANTIDAD_X_DISTRIB, CANTIDAD_DISTRIB "                                             
                sSql = sSql & " FROM FL_REL_DISTRIB_PEJ WHERE ID_PARTIDA_EJ =  " & idPEJ
                
                dtMontos = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return nothing
            End If


            If dtMontos.Rows.Count  = 1 THEN 
                 Return   dtMontos 
            Else
                 sMsgError = "Multiples valores en la operación"
                 Return Nothing 
            End If

        Catch ex As Exception    
            Return Nothing 
        End Try     
    
     End Function
  

        Public Shared Function ObtenerMontosInterXPAJsDistribDB(ByVal idPEJ As Long, ByRef sMsgError As String ) As decimal 

        
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
       
        

        Dim dtMonto As New DataTable()

          Try                
                
                       sSql = " SELECT SUM(CANTIDAD_AJUS) AS TOTAL_AJUST  FROM FL_OP_PAJ WHERE ID_PARTIDA_EJ = " & idPEJ                                             
                sSql = sSql & " AND ESTATUS IN (" & CTPartidaAjuste.enumEstatusPAJ.CREADA & "," & CTPartidaAjuste.enumEstatusPAJ.ENVIADA  & ")"  
                
                dtMonto = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return 0
            End If


            If dtMonto.Rows.Count  = 1 THEN 
                 Return   dtMonto.Rows(0)("TOTAL_AJUST")
            Else
                 sMsgError = "Multiples valores en la operación"
                 Return 0 
            End If

        Catch ex As Exception    
            sMsgError = ex.Message 
            Return 0
        End Try     
    
     End Function
           

   
        Public Shared Function ObtenerFondosXintermediarioYTVNGDB(ByVal idIntermediario As Integer, ByVal idTvng As Integer,ByRef sMsgError As String ) As DataTable 

          Dim db As New CBaseDeDatosSQLServer()
          Dim pm As New List(Of SqlParameter)
          Dim Sql = New StringBuilder ()
                  

         Dim dtFondos As New DataTable()


         Sql.Append ("SELECT A.FONDO, B.CTO_INTERMEDIARIO  FROM FL_REL_FONDO_TVNG  A INNER JOIN FL_REL_FONDO_INTERMEDIARIO B ON (A.FONDO = B.FONDO ) WHERE B.ID_INTERMEDIARIO = ")
         Sql.Append (idIntermediario)
         Sql.Append (" AND A.ID_TVNG = ")
         Sql.Append(idTvng)
    
         Try

              dtFondos = db.EjecutarConsulta(Sql.ToString (), pm, sMsgError)
              
              If sMsgError.Equals("")                 
                 Return dtFondos 
              else
                 Return Nothing                
              End If

         Catch ex As Exception
             sMsgError = ex.Message 
            Return Nothing 
         End Try


      End Function
  


     Public Shared Function ObtenerFondosXTVNGDB(ByVal idTvng As Integer,ByRef sMsgError As String ) As DataTable 

          Dim db As New CBaseDeDatosSQLServer()
          Dim pm As New List(Of SqlParameter)
          Dim Sql = New StringBuilder ()
                  

         Dim dtFondos As New DataTable()


         Sql.Append ("SELECT A.FONDO  FROM FL_REL_FONDO_TVNG  A WHERE A.ID_TVNG = ")                  
         Sql.Append(idTvng)
    
         Try

              dtFondos = db.EjecutarConsulta(Sql.ToString (), pm, sMsgError)
              
              If sMsgError.Equals("")                 
                 Return dtFondos 
              else
                 Return Nothing                
              End If

         Catch ex As Exception
             sMsgError = ex.Message 
            Return Nothing 
         End Try


      End Function



  
        Public Shared Function ObtenerProxSecDePEJFondoInter(ByVal idPEJ As Long) As Integer 

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtFolio As New DataTable()

        Try
                sSql = " SELECT (ISNULL(MAX(SEC_DISTRIB),0)+ 1) as folioSEC FROM FL_OP_DISTRIB_FONDOINTER WHERE ID_PARTIDA_EJ = " & idPEJ                              
                dtFolio = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return -1
            End If


            If dtFolio.Rows(0)("folioSEC") > 0 THEN 
                 Return dtFolio.Rows(0)("folioSEC")
            Else
                 Return -1
            End If

        Catch ex As Exception    
            Return -1
        End Try     
     

      End Function
  
        
        Public Shared Function AlmacenarInfoDistribPEJ(ByRef InterDistrib As CTDistribLiqReduc, ByRef sMsgError As String) As Boolean 

         Dim db      As New CBaseDeDatosSQLServer()
         Dim pm      As New List(Of SqlParameter)         
         Dim regAfec As Integer = 0     
         
         Dim idSecDistrib As Integer = 0 

         idSecDistrib = ObtenerProxSecDePEJFondoInter (InterDistrib.IdPEJ)

         If idSecDistrib <= 0 then 
           sMsgError = "No se pudo obtener el folio de la secuencia de distribución de la PEJ: " & InterDistrib.IdPEJ 
           Return False 
         End If

        
                  
          Dim sql        = New  StringBuilder ("")

          sql.Append (" INSERT INTO FL_OP_DISTRIB_FONDOINTER (ID_PARTIDA_EJ,SEC_DISTRIB,FOLIO_ORDEN,FEC_REGISTRO,CANTIDAD_DISTRIBUIDA,ENVIADO_DECALOG, ESTATUS_RECUP_FOLIO) VALUES (")
          sql.Append (InterDistrib.IdPEJ)
          sql.Append (", ")
          sql.Append (idSecDistrib)
          sql.Append (",null,CURRENT_TIMESTAMP,")
          sql.Append (InterDistrib.MontoTotalDistrib)
          sql.Append (",0,0);")
          
          sql.AppendLine
          
          For Each distrib As CTDistribLiqReduc.structDistribFondoXInter In InterDistrib.LstDistrib 
            

              If distrib.MontoDistrib > 0 then 

                  sql.Append( " INSERT INTO FL_OP_CANTIDADXFONDO (ID_PARTIDA_EJ,SEC_DISTRIB,FONDO,CANTIDAD) VALUES ( " )
                  sql.Append(InterDistrib.IdPEJ)
                  sql.Append(", ")
                  sql.Append(idSecDistrib)
                  sql.Append (",'")
                  sql.Append (distrib.CodFondo)
                  sql.Append ("',")
                  sql.Append (distrib.MontoDistrib )
                  sql.Append ("); ")
              
              sql.AppendLine 

              End If 

          Next


          'Actualizar importes de distribución 

          sql.Append(" UPDATE FL_REL_DISTRIB_PEJ SET CANTIDAD_X_DISTRIB  = (CANTIDAD_X_DISTRIB -  ")  
          sql.Append(InterDistrib.MontoTotalDistrib) 
          sql.Append(" ), ")
          sql.Append(" CANTIDAD_DISTRIB = (CANTIDAD_DISTRIB + ")
          sql.Append(InterDistrib.MontoTotalDistrib)
          sql.Append(" ) WHERE ID_PARTIDA_EJ = ")
          sql.Append(InterDistrib.IdPEJ)
          sql.Append ("; ")
          sql.AppendLine 



           'Actualización del estatus de la distribución           
          sql.Append(" UPDATE FL_REL_DISTRIB_PEJ SET ESTATUS_DIST = ")
          If InterDistrib.EsParcial = True
             sql.Append (CTEstadoPEJ.enumEstatusPEJ.PARCIAL)                                               
          else
             sql.Append (CTEstadoPEJ.enumEstatusPEJ.COMPLETA)                                               
          End If
          sql.Append(" WHERE ID_PARTIDA_EJ = ")
          sql.Append (InterDistrib.IdPEJ)                                               
          sql.Append ("; ")
          sql.AppendLine 
     
      
          'Actualización del estado y monto actual de la PS 
           sql.Append(" UPDATE FL_REL_BSO_PS SET CANTIDAD_ACTUAL = (CANTIDAD_ACTUAL -  ")
           sql.Append(InterDistrib.MontoTotalDistrib)
           sql.Append("), ESTATUS_PARTIDA = ") 
           sql.Append(CTPartidaSolicitud.enumEstatusPS.EN_DISTRIBUCION )
           sql.Append(" WHERE ID_PARTIDA_PS =  ")
           sql.Append (InterDistrib.IdPs )                                               
           sql.Append ("; ")
           sql.AppendLine 
           
          'Inserta registro para envio de cartas
          sql.Append (" INSERT INTO FL_OP_ENVIO_CARTAS VALUES (")
          sql.Append (InterDistrib.IdPEJ & ", " & idSecDistrib & ",null," & CTEnvioCarta.enumEstatusCarta.NO_ENVIADA & ",11")
          sql.Append ("); ")
          
          sql.AppendLine

          If db.EjecutarComando(Sql.ToString() , pm,regAfec , sMsgError) = True Then            
               Return True
          Else
               Return false       
          End If
            
      End Function
 
    Public Shared Function ActualizaArchivoEstatusDecalog(ByVal idPej As Integer, ByVal sec As Integer, ByRef nomArchivo As String, ByRef sMsgError As String) As Boolean 
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        sSql = "update FL_OP_DISTRIB_FONDOINTER " _
               & " set ENVIADO_DECALOG = 1, NOM_ARCHIVO = '" & nomArchivo & "', " _
               & " ESTATUS_RECUP_FOLIO = " &  CTDistribucionGL.enumEstatusFolioRecupDecalog.NO_RECUPERADO & "," _
               & " OBSERVACION_ENV_DEC = ''" _
               & " where SEC_DISTRIB = " & sec.ToString() _
               & " and ID_PARTIDA_EJ = " & idPej.ToString()
       
        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

            
      End Function

    Public Shared Function ObtieneEstatusEnvioDecalog(ByVal idPej As Integer, ByVal sec As Integer) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dt As New DataTable()

        Try
                sSql = " SELECT ENVIADO_DECALOG FROM FL_OP_DISTRIB_FONDOINTER WHERE ID_PARTIDA_EJ = " & idPej & " AND SEC_DISTRIB = " & sec                              
                dt = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            End If

            Return Convert.ToBoolean(dt.Rows(0)("ENVIADO_DECALOG"))

        Catch ex As Exception    
            Return False
        End Try     
     

   End Function
  

   Public Shared Function ObtieneEstatusEnvioDecalogPS(ByVal idPs) As Integer

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dt As New DataTable()

        Try
                sSql = " select COUNT(*) CANTIDAD_ENVIADO from FL_OP_DISTRIB_FONDOINTER df, FL_REL_PS_PEJ pspe " & _
                       " where df.ID_PARTIDA_EJ = pspe.ID_PARTIDA_EJ " & _
                       " and df.ENVIADO_DECALOG = 1 and pspe.ID_PARTIDA_PS = " & idPs                              
                dt = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Return False
            End If

            Return Convert.ToInt32(dt.Rows(0)("CANTIDAD_ENVIADO"))

        Catch ex As Exception    
            Return False
        End Try     
     

   End Function
  #End Region



  #Region "Envio de cartas a intermediarios"
  
      Public Shared  Function ObtieneCartasHistoricas(ByVal fechaSolicitud As Date, ByVal tvng As String) As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtPE As New DataTable()

        Try
            sSql = "select ps.ID_PARTIDA_PS, ps.TVNG ID_TV_NG, tv.VALOR TV_NG, bsops.CANTIDAD_ACTUAL, ec.ID_PARTIDA_EJ, ec.SEC_DISTRIB, pe.INTERMEDIARIO ID_INTER," _
		         & " inte.VALOR INTER, pe.TASA, ps.FEC_SOLICITUD, ps.FEC_LIQUIDACION, df.CANTIDAD_DISTRIBUIDA," _
		         & " es.VALOR ESTATUS, case when ec.FEC_ENVIO is null then '' else convert(varchar(10), ec.FEC_ENVIO,103) + ' ' + convert(varchar(10), ec.FEC_ENVIO,108) end FECHA_ENV," _
		         & " ps.PLAZO, isnull(df.FOLIO_ORDEN,'') FOLIO_ORDEN" _
                 & " from FL_OP_ENVIO_CARTAS ec, FL_OP_DISTRIB_FONDOINTER df, FL_REL_PS_PEJ peps, FL_OP_PEJ pe," _
	             & " FL_REL_DISTRIB_PEJ dpe, FL_REL_BSO_PS bsops, FL_OP_PS ps, GEN_CAT_GEN tv, GEN_CAT_GEN inte," _
	             & " GEN_CAT_GEN es" _
                 & " where ec.ID_PARTIDA_EJ = df.ID_PARTIDA_EJ" _
                 & " and ec.SEC_DISTRIB = df.SEC_DISTRIB" _
                 & " and ec.ID_PARTIDA_EJ = peps.ID_PARTIDA_EJ" _
                 & " and pe.ID_PARTIDA_EJ = peps.ID_PARTIDA_EJ" _
                 & " and dpe.ID_PARTIDA_EJ = pe.ID_PARTIDA_EJ" _
                 & " and peps.ID_PARTIDA_PS = ps.ID_PARTIDA_PS" _
                 & " and bsops.ID_PARTIDA_PS = ps.ID_PARTIDA_PS" _
                 & " and tv.ID_CAT = ps.CAT_TVNG" _
                 & " and tv.CLAVE = ps.TVNG" _
                 & " and pe.CAT_INTERMEDIARIO = inte.ID_CAT" _
                 & " and pe.INTERMEDIARIO = inte.CLAVE" _
                 & " and ec.CAT_ESTATUS = es.ID_CAT" _
                 & " and ec.ESTATUS = es.CLAVE" _
                 & " and bsops.ESTATUS_PARTIDA not in (" & CTPartidaSolicitud.enumEstatusPS.CANCELADA & "," & CTPartidaSolicitud.enumEstatusPS.CANCELADA_ACEPTADA & ")" _
                 & " and dpe.ESTATUS_DIST in (" & CTEstadoPEJ.enumEstatusPEJ.PARCIAL & "," & CTEstadoPEJ.enumEstatusPEJ.COMPLETA & ")" _
                 & " and convert(varchar(10),ps.FEC_SOLICITUD,103) = '" & fechaSolicitud.ToString("dd/MM/yyyy") & "'" _
                 & " and ps.TVNG like '" & tvng & "'"


            dtPE = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtPE

        Catch ex As Exception
            Throw ex
        End Try
    End Function
  
      Public Shared  Function ObtieneDetalleCarta(ByVal idPej As Long, ByVal secuencia As Long, ByVal idIntermediario As Long) As DataTable
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtDPE As New DataTable()

        Try
            sSql = "select cf.FONDO, cf.CANTIDAD, f.CTO_INDEVAL, isnull(fi.CTO_INTERMEDIARIO, 'NO CTO') CTO_INTERMEDIARIO" _
                 & " from FL_OP_CANTIDADXFONDO cf, FL_CAT_FONDO f right join FL_REL_FONDO_INTERMEDIARIO fi" _
	             & " on fi.FONDO = f.FONDO" _
	             & " where f.FONDO = cf.FONDO" _
                 & " and cf.ID_PARTIDA_EJ = " & idPej.ToString() _
                 & " and cf.SEC_DISTRIB = " & secuencia.ToString()  _
                 & " and fi.ID_INTERMEDIARIO = " & idIntermediario.ToString() _
                 & " order by cf.FONDO"


            dtDPE = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtDPE

        Catch ex As Exception
            Throw ex
        End Try
    End Function
  
      Public Shared Function ActualizaCarta(ByVal idPej As Integer, ByVal secuencia As Integer, ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer


        sSql = "update ec" _
             & " set ec.FEC_ENVIO = getdate(), ec.ESTATUS = " & CTEnvioCarta.enumEstatusCarta.ENVIADA _
             & " from FL_OP_ENVIO_CARTAS ec" _
             & " where ec.ID_PARTIDA_EJ = " & idPej _
             & " and ec.SEC_DISTRIB = " & secuencia

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function
  
      Public Shared  Function ObtenerDireccionesMailXIntermediario(ByVal idIntermediario As Integer, ByRef sMsgError As String) As List(Of String) 

        
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        
        Dim lstDirMail  = New  List(Of String )


        Dim dtDirMail As New DataTable()
                                    
        Try

                sSql = " SELECT DIRECCION_MAIL FROM FL_PARAM_CARTA_ENVIO WHERE ID_INTERMEDIARIO =  "  & idIntermediario                                                                            
                dtDirMail = db.EjecutarConsulta(sSql, pm, sMsgError)


            If Not sMsgError.Equals("") Then
                Return nothing
            End If


            For Each  registro As DataRow In dtDirMail.Rows                
                  lstDirMail.Add(registro("DIRECCION_MAIL"))
            Next


            Return lstDirMail 


        Catch ex As Exception
             Return Nothing 
        End Try

  End Function
  
  #End Region


  #Region "MONITOR DE FLUJO DE LIQUIDEZ"
    
    
        Public Shared Function ObtenerBSOMonitor(ByVal pfecha As Date) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim Sql  = New StringBuilder ("")
        Dim sMsgError As String = ""

        Dim dtBSOMonitor As New DataTable()

        Try
                
            Sql.Append (" SELECT A.ID_BSO, A.FEC_CREACION, A.FEC_OPERA, B.VALOR as ESTATUS, C.NOMBRE    ")
            Sql.Append (" FROM FL_OP_BSO A  ")
            Sql.Append (" INNER JOIN GEN_CAT_GEN B ON (A.CAT_ESTATUS_BSO = B.ID_CAT  AND A.ESTATUS_BSO = CLAVE) ")
            Sql.Append (" INNER JOIN MMA_CAT_USUARIO C ON (A.USU_GESTOR = C.ID) ")
            Sql.Append (" WHERE CONVERT(DATE, FEC_OPERA) = '")
            Sql.Append (pfecha.ToString("yyyy-MM-dd"))
             Sql.Append ("'")

            dtBSOMonitor = db.EjecutarConsulta(Sql.ToString (), pm, sMsgError)

            If Not sMsgError.Equals("") Then                   
               Throw New Exception (sMsgError)
            End If

            Return dtBSOMonitor

        Catch ex As Exception    
            Throw ex
        End Try

End Function
        
        Public Shared Function ObtenerPSMonitorXBSODB(ByVal idBSO As long) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim Sql  = New StringBuilder ("")
        Dim sMsgError As String = ""

        Dim dtPSMonitor As New DataTable()

        Try
                
            Sql.Append (" SELECT  A.ID_PARTIDA_PS,B.ESTATUS_PARTIDA, C.VALOR AS TVNG,A.FEC_SOLICITUD, A.FEC_LIQUIDACION, ISNULL(A.PLAZO,0) AS PLAZO,A.COMENTARIO,ISNULL(A.CANTIDAD_SOL,0) AS MONTO_SOL, ")
            Sql.Append (" ISNULL(B.CANTIDAD_ACTUAL,0) AS MONTO_ACTUAL, D.VALOR AS ESTATUS, ISNULL(E.HORA_INGRESO,'') AS HORA_INGRESO, ISNULL(E.HORA_EJECUCION, '') AS HORA_EJECUCION  ")
            Sql.Append (" FROM FL_OP_PS A ")
            Sql.Append (" INNER JOIN FL_REL_BSO_PS B ON (A.ID_PARTIDA_PS = B.ID_PARTIDA_PS) ")
            Sql.Append (" INNER JOIN GEN_CAT_GEN C ON (A.CAT_TVNG = C.ID_CAT  AND A.TVNG = C.CLAVE) ")
            Sql.Append (" INNER JOIN GEN_CAT_GEN D ON (B.CAT_ESTATUS_PARTIDA  = D.ID_CAT   AND  B.ESTATUS_PARTIDA  = D.CLAVE ) ")
            Sql.Append (" LEFT OUTER JOIN FL_REL_PS_PROC_EJECT E ON (E.ID_PARTIDA_PS = A.ID_PARTIDA_PS) ")
            Sql.Append (" WHERE B.ID_BSO = ")
            Sql.Append (idBSO)
       

            dtPSMonitor = db.EjecutarConsulta(Sql.ToString (), pm, sMsgError)

            If Not sMsgError.Equals("") Then                   
               Throw New Exception (sMsgError)
            End If

            Return dtPSMonitor

        Catch ex As Exception    
            Throw ex
        End Try

End Function
        
        Public Shared Function ObtenerInfoGralPEJXPS(ByVal idPS As Long) As DataTable

 Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim Sql  = New StringBuilder ("")
        Dim sMsgError As String = ""

        Dim dtPEJMonitor As New DataTable()

        Try
                
            Sql.Append (" SELECT A.ID_PARTIDA_EJ,A.TASA,B.VALOR AS INTERMEDIARIO,A.CANTIDAD AS MONTO_DISTRIB_ORI,C.CANTIDAD_DISTRIB,C.CANTIDAD_X_DISTRIB,D.VALOR AS ESTATUS, ISNULL(A.MEDIO,'') AS MEDIO, ")
            Sql.Append (" CASE A.FLEXIBLE WHEN 1 THEN 'Si' WHEN 2 THEN 'No' ELSE 'No' END AS FLEXIBLE, ISNULL(A.OBSERVACIONES,'') AS OBSERVACIONES  ")
            Sql.Append (" FROM FL_OP_PEJ A  ")
            Sql.Append (" INNER JOIN GEN_CAT_GEN B ON (A.CAT_INTERMEDIARIO = B.ID_CAT AND A.INTERMEDIARIO  = B.CLAVE )  ")
            Sql.Append (" INNER JOIN FL_REL_DISTRIB_PEJ C ON (C.ID_PARTIDA_EJ = A.ID_PARTIDA_EJ ) ")
            Sql.Append (" INNER JOIN GEN_CAT_GEN D ON (C.CAT_ESTATUS_DIST = D.ID_CAT AND C.ESTATUS_DIST = D.CLAVE)  ")
            Sql.Append (" INNER JOIN FL_REL_PS_PEJ E ON (E.ID_PARTIDA_EJ = A.ID_PARTIDA_EJ AND E.ID_PARTIDA_PS =")
            Sql.Append (idPS)
            Sql.Append (") ")

                   
            dtPEJMonitor = db.EjecutarConsulta(Sql.ToString (), pm, sMsgError)

            If Not sMsgError.Equals("") Then                   
               Throw New Exception (sMsgError)
            End If

            Return dtPEJMonitor

        Catch ex As Exception    
            Throw ex
        End Try

End Function
        
        Public Shared Function ObtenerInfoGralDistribXPEJ(ByVal idPEJ As long) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim Sql  = New StringBuilder ("")
        Dim sMsgError As String = ""

        Dim dtPEJDistrib As New DataTable()

        Try
                
            Sql.Append (" SELECT ID_PARTIDA_EJ, SEC_DISTRIB,isnull(FOLIO_ORDEN,'') as FOLIO_ORDEN,FEC_REGISTRO, ISNULL(CANTIDAD_DISTRIBUIDA,0) AS CANTIDAD_DISTRIBUIDA, ENVIADO_DECALOG, ESTATUS_RECUP_FOLIO  ")
            Sql.Append (" FROM FL_OP_DISTRIB_FONDOINTER WHERE ID_PARTIDA_EJ =  ")
            Sql.Append (idPEJ)
          
                   
            dtPEJDistrib = db.EjecutarConsulta(Sql.ToString (), pm, sMsgError)

            If Not sMsgError.Equals("") Then                   
               Throw New Exception (sMsgError)
            End If

            Return dtPEJDistrib

        Catch ex As Exception    
            Throw ex
        End Try  

End Function
        
        Public Shared Function ObtenerInfoDistribDetalleXPEJ(ByVal idPEJ As Long, ByVal idSec As Integer) As DataTable 

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim Sql  = New StringBuilder ("")
        Dim sMsgError As String = ""

        Dim dtPEJDistribDet As New DataTable()

        Try
                
            Sql.Append (" SELECT FONDO,CANTIDAD FROM FL_OP_CANTIDADXFONDO WHERE ID_PARTIDA_EJ =  ")
            Sql.Append (idPEJ)
            Sql.Append (" AND SEC_DISTRIB = ")
            Sql.Append (idSec)
          
                   
            dtPEJDistribDet = db.EjecutarConsulta(Sql.ToString (), pm, sMsgError)

            If Not sMsgError.Equals("") Then                   
               Throw New Exception (sMsgError)
            End If

            Return dtPEJDistribDet

        Catch ex As Exception    
            Throw ex
        End Try  


End Function
        
        Public Shared Function ObtenerPAJMonitorXPEJ( ByVal idPEJ As Long)

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim Sql  = New StringBuilder ("")
        Dim sMsgError As String = ""

        Dim dtPAJ As New DataTable()

        Try
                
            Sql.Append (" SELECT A.ID_PARTIDA_PS, A.ID_PARTIDA_EJ, A.ID_PARTIDA_AJUSTE, B.VALOR as ESTATUS,  A.FEC_CREACION, ISNULL(A.FEC_APLICACION,'') AS FEC_APLICACION , A.CANTIDAD_AJUS, ISNULL( A.COMENTARIO,'') AS COMENTARIO ")
            Sql.Append (" FROM FL_OP_PAJ A INNER JOIN GEN_CAT_GEN B ON (A.CAT_ESTATUS_PAJ = B.ID_CAT AND A.ESTATUS = B.CLAVE) ")
            Sql.Append (" WHERE A.ID_PARTIDA_EJ =  ")
            Sql.Append (idPEJ)
                             
            dtPAJ = db.EjecutarConsulta(Sql.ToString (), pm, sMsgError)

            If Not sMsgError.Equals("") Then                   
               Throw New Exception (sMsgError)
            End If

            Return dtPAJ

        Catch ex As Exception    
            Throw ex
        End Try  


End Function        
        
        Public Shared Function ObtenerDescribEstatusPS()

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim Sql  = New StringBuilder ("")
        Dim sMsgError As String = ""

        Dim dtEstatusPS As New DataTable()

        Try
                
            Sql.Append (" SELECT VALOR AS ESTATUS FROM GEN_CAT_GEN WHERE ID_CAT = ")           
            Sql.Append (__CLAVE_CAT_ESTATUS_PS__ )
                             
            dtEstatusPS = db.EjecutarConsulta(Sql.ToString (), pm, sMsgError)

            If Not sMsgError.Equals("") Then                   
               Throw New Exception (sMsgError)
            End If

            Return dtEstatusPS

        Catch ex As Exception    
            Throw ex
        End Try  


End Function
    

        
        
        Public Shared Function ObtenerBSOInfoExp(ByVal pfechaInI As Date,ByVal pfechaFin As Date) As DataTable

                Dim db As New CBaseDeDatosSQLServer()
                Dim pm As New List(Of SqlParameter)
                Dim Sql  = New StringBuilder ("")
                Dim sMsgError As String = ""

                Dim dtBSOMonitor As New DataTable()

                Try
                        
                    Sql.Append (" SELECT '0' AS 'RAIZ', A.ID_BSO, A.FEC_CREACION, A.FEC_OPERA, B.VALOR as ESTATUS, C.NOMBRE ")
                    Sql.Append (" FROM FL_OP_BSO A ")
                    Sql.Append (" INNER JOIN GEN_CAT_GEN B ON (A.CAT_ESTATUS_BSO = B.ID_CAT  AND A.ESTATUS_BSO = CLAVE)  ")
                    Sql.Append (" INNER JOIN MMA_CAT_USUARIO C ON (A.USU_GESTOR = C.ID) ")
                    Sql.Append (" WHERE CONVERT(DATE, FEC_OPERA) between '")
                    Sql.Append (pfechaInI.ToString("yyyy-MM-dd"))
                    Sql.Append ("' AND '")
                    Sql.Append (pfechaFin.ToString("yyyy-MM-dd"))
                    Sql.Append ("' ORDER BY A.ID_BSO")

                    dtBSOMonitor = db.EjecutarConsulta(Sql.ToString (), pm, sMsgError)

                    If Not sMsgError.Equals("") Then                   
                       Return New datatable ()
                    End If

                    Return dtBSOMonitor

                Catch ex As Exception    
                    Return New datatable ()
                End Try

         End Function

        Public Shared Function ObtenerPSInfoExp(ByVal pfechaInI As Date,ByVal pfechaFin As Date) As DataTable

          Dim db As New CBaseDeDatosSQLServer()
          Dim pm As New List(Of SqlParameter)
          Dim Sql  = New StringBuilder ("")
          Dim sMsgError As String = ""

          Dim dtPS As New DataTable()

          Try
                  
              Sql.Append (" SELECT B.ID_BSO,  A.ID_PARTIDA_PS, C.VALOR AS TVNG,A.FEC_SOLICITUD, A.FEC_LIQUIDACION, ISNULL(A.PLAZO,0) AS PLAZO,A.COMENTARIO,ISNULL(A.CANTIDAD_SOL,0) AS MONTO_SOL,  ")
              Sql.Append (" ISNULL(B.CANTIDAD_ACTUAL,0) AS MONTO_ACTUAL, D.VALOR AS ESTATUS, E.HORA_INGRESO, E.HORA_EJECUCION ")
              Sql.Append (" FROM FL_OP_PS A ")
              Sql.Append (" INNER JOIN FL_REL_BSO_PS B ON (A.ID_PARTIDA_PS = B.ID_PARTIDA_PS)  ")
              Sql.Append (" INNER JOIN GEN_CAT_GEN C ON (A.CAT_TVNG = C.ID_CAT  AND A.TVNG = C.CLAVE) ")
              Sql.Append (" INNER JOIN GEN_CAT_GEN D ON (B.CAT_ESTATUS_PARTIDA  = D.ID_CAT   AND  B.ESTATUS_PARTIDA  = D.CLAVE )  ")
              Sql.Append (" LEFT OUTER JOIN FL_REL_PS_PROC_EJECT E ON (E.ID_PARTIDA_PS = A.ID_PARTIDA_PS) ")
              Sql.Append (" WHERE B.ID_BSO in  (SELECT A.ID_BSO FROM FL_OP_BSO A WHERE CONVERT(DATE, A.FEC_OPERA) between '")
              Sql.Append (pfechaInI.ToString("yyyy-MM-dd"))
              Sql.Append ("' AND '")
              Sql.Append (pfechaFin.ToString("yyyy-MM-dd"))
              Sql.Append ("') order by B.ID_BSO,  A.ID_PARTIDA_PS ASC")

            

              dtPS = db.EjecutarConsulta(Sql.ToString (), pm, sMsgError)

              If Not sMsgError.Equals("") Then                   
                 Return New datatable ()
              End If

              Return dtPS

          Catch ex As Exception    
              Return New datatable ()
          End Try

         End Function

        Public Shared Function ObtenerPEJInfoExp(ByVal pfechaInI As Date,ByVal pfechaFin As Date) As DataTable

          Dim db As New CBaseDeDatosSQLServer()
          Dim pm As New List(Of SqlParameter)
          Dim Sql  = New StringBuilder ("")
          Dim sMsgError As String = ""

          Dim dtPEJ As New DataTable()

          Try
                  
              Sql.Append (" SELECT E.ID_PARTIDA_PS,  A.ID_PARTIDA_EJ,A.TASA,B.VALOR AS INTERMEDIARIO,A.CANTIDAD AS MONTO_DISTRIB_ORI,C.CANTIDAD_DISTRIB,C.CANTIDAD_X_DISTRIB,D.VALOR AS ESTATUS, ISNULL(A.MEDIO,'') AS MEDIO, ")
              Sql.Append (" CASE A.FLEXIBLE WHEN 1 THEN 'Si' WHEN 2 THEN 'No' ELSE 'No' END AS FLEXIBLE, ISNULL(A.OBSERVACIONES,'') AS OBSERVACIONES  ")
              Sql.Append (" FROM FL_OP_PEJ A  ")
              Sql.Append (" INNER JOIN GEN_CAT_GEN B ON (A.CAT_INTERMEDIARIO = B.ID_CAT AND A.INTERMEDIARIO  = B.CLAVE ) ")
              Sql.Append (" INNER JOIN FL_REL_DISTRIB_PEJ C ON (C.ID_PARTIDA_EJ = A.ID_PARTIDA_EJ ) ")
              Sql.Append (" INNER JOIN GEN_CAT_GEN D ON (C.CAT_ESTATUS_DIST = D.ID_CAT AND C.ESTATUS_DIST = D.CLAVE) ")
              Sql.Append (" INNER JOIN FL_REL_PS_PEJ E ON (E.ID_PARTIDA_EJ = A.ID_PARTIDA_EJ AND E.ID_PARTIDA_PS in(SELECT  A.ID_PARTIDA_PS FROM FL_OP_PS A ")
              Sql.Append (" INNER JOIN FL_REL_BSO_PS B ON (A.ID_PARTIDA_PS = B.ID_PARTIDA_PS) ")
              Sql.Append (" WHERE B.ID_BSO in  (SELECT A.ID_BSO FROM FL_OP_BSO A WHERE CONVERT(DATE, A.FEC_OPERA) between '")
              Sql.Append (pfechaInI.ToString("yyyy-MM-dd"))
              Sql.Append ("' AND '")
              Sql.Append (pfechaFin.ToString("yyyy-MM-dd"))
              Sql.Append ("'))) order by E.ID_PARTIDA_PS,  A.ID_PARTIDA_EJ asc")

  

              dtPEJ = db.EjecutarConsulta(Sql.ToString (), pm, sMsgError)

              If Not sMsgError.Equals("") Then                   
                 Return New datatable ()
              End If

              Return dtPEJ

          Catch ex As Exception    
              Return New datatable ()
          End Try



         End Function
         
        Public Shared Function ObtenerPAJInfoExp(ByVal pfechaInI As Date,ByVal pfechaFin As Date) As DataTable

          Dim db As New CBaseDeDatosSQLServer()
          Dim pm As New List(Of SqlParameter)
          Dim Sql  = New StringBuilder ("")
          Dim sMsgError As String = ""

          Dim dtPAJ As New DataTable()

          Try
                  
              Sql.Append (" SELECT A.ID_PARTIDA_EJ, A.ID_PARTIDA_AJUSTE, B.VALOR as ESTATUS,  A.FEC_CREACION, ISNULL(A.FEC_APLICACION,'') AS FEC_APLICACION , A.CANTIDAD_AJUS, ISNULL( A.COMENTARIO,'') AS COMENTARIO ")
              Sql.Append (" FROM FL_OP_PAJ A INNER JOIN GEN_CAT_GEN B ON (A.CAT_ESTATUS_PAJ = B.ID_CAT AND A.ESTATUS = B.CLAVE)  ")
              Sql.Append (" WHERE A.ID_PARTIDA_EJ ")
              Sql.Append (" in (SELECT A.ID_PARTIDA_EJ FROM FL_OP_PEJ A   ")
              Sql.Append (" INNER JOIN FL_REL_PS_PEJ E ON (E.ID_PARTIDA_EJ = A.ID_PARTIDA_EJ AND E.ID_PARTIDA_PS in(SELECT  A.ID_PARTIDA_PS FROM FL_OP_PS A  ")
              Sql.Append (" INNER JOIN FL_REL_BSO_PS B ON (A.ID_PARTIDA_PS = B.ID_PARTIDA_PS)  ")
              Sql.Append (" WHERE B.ID_BSO in  (SELECT A.ID_BSO FROM FL_OP_BSO A WHERE CONVERT(DATE, A.FEC_OPERA) between '")
                        
              Sql.Append (pfechaInI.ToString("yyyy-MM-dd"))
              Sql.Append ("' AND '")
              Sql.Append (pfechaFin.ToString("yyyy-MM-dd"))
              Sql.Append ("'))))")
           

              dtPAJ = db.EjecutarConsulta(Sql.ToString (), pm, sMsgError)

              If Not sMsgError.Equals("") Then                   
                 Return New datatable ()
              End If

              Return dtPAJ

          Catch ex As Exception    
              Return New datatable ()
          End Try



         End Function
         
        Public Shared Function ObtenerDistribInfoExp(ByVal pfechaInI As Date,ByVal pfechaFin As Date) As DataTable

          Dim db As New CBaseDeDatosSQLServer()
          Dim pm As New List(Of SqlParameter)
          Dim Sql  = New StringBuilder ("")
          Dim sMsgError As String = ""

          Dim dtDistrib As New DataTable()

          Try
                  
              Sql.Append (" SELECT ID_PARTIDA_EJ, SEC_DISTRIB,isnull(FOLIO_ORDEN,'') as FOLIO_ORDEN,FEC_REGISTRO, ISNULL(CANTIDAD_DISTRIBUIDA,0) AS CANTIDAD_DISTRIBUIDA, ENVIADO_DECALOG  ")
              Sql.Append (" FROM FL_OP_DISTRIB_FONDOINTER WHERE ID_PARTIDA_EJ  ")
              Sql.Append (" IN (SELECT A.ID_PARTIDA_EJ ")
              Sql.Append (" FROM FL_OP_PEJ A ")
              Sql.Append (" INNER JOIN FL_REL_PS_PEJ E ON (E.ID_PARTIDA_EJ = A.ID_PARTIDA_EJ AND E.ID_PARTIDA_PS in(SELECT  A.ID_PARTIDA_PS FROM FL_OP_PS A  ")
              Sql.Append (" INNER JOIN FL_REL_BSO_PS B ON (A.ID_PARTIDA_PS = B.ID_PARTIDA_PS)  ")
              Sql.Append (" WHERE B.ID_BSO in  (SELECT A.ID_BSO FROM FL_OP_BSO A WHERE CONVERT(DATE, A.FEC_OPERA) between '")
         
             
              Sql.Append (pfechaInI.ToString("yyyy-MM-dd"))
              Sql.Append ("' AND '")
              Sql.Append (pfechaFin.ToString("yyyy-MM-dd"))
              Sql.Append ("'))) ) ORDER BY ID_PARTIDA_EJ, SEC_DISTRIB")
           


             Sql.Append ("  ")


              dtDistrib = db.EjecutarConsulta(Sql.ToString (), pm, sMsgError)

              If Not sMsgError.Equals("") Then                   
                 Return New datatable ()
              End If

              Return dtDistrib

          Catch ex As Exception    
              Return New datatable ()
          End Try



         End Function        

        Public Shared Function ObtenerDistribFIInfoExp(ByVal pfechaInI As Date,ByVal pfechaFin As Date) As DataTable

          Dim db As New CBaseDeDatosSQLServer()
          Dim pm As New List(Of SqlParameter)
          Dim Sql  = New StringBuilder ("")
          Dim sMsgError As String = ""

          Dim dtDistribFI As New DataTable()

          Try
                  
              Sql.Append (" SELECT A.ID_PARTIDA_EJ, A.SEC_DISTRIB, FONDO,CANTIDAD FROM FL_OP_CANTIDADXFONDO A INNER JOIN FL_OP_DISTRIB_FONDOINTER B ON (A.ID_PARTIDA_EJ = B.ID_PARTIDA_EJ AND A.SEC_DISTRIB = B.SEC_DISTRIB) WHERE A.ID_PARTIDA_EJ ")
              Sql.Append (" in (SELECT A.ID_PARTIDA_EJ FROM FL_OP_PEJ A   ")
              Sql.Append (" INNER JOIN FL_REL_PS_PEJ E ON (E.ID_PARTIDA_EJ = A.ID_PARTIDA_EJ AND E.ID_PARTIDA_PS in(SELECT  A.ID_PARTIDA_PS FROM FL_OP_PS A  ")
              Sql.Append (" INNER JOIN FL_REL_BSO_PS B ON (A.ID_PARTIDA_PS = B.ID_PARTIDA_PS)  ")
              Sql.Append (" WHERE B.ID_BSO in  (SELECT A.ID_BSO FROM FL_OP_BSO A WHERE CONVERT(DATE, A.FEC_OPERA) between '")
              
             
              Sql.Append (pfechaInI.ToString("yyyy-MM-dd"))
              Sql.Append ("' AND '")
              Sql.Append (pfechaFin.ToString("yyyy-MM-dd"))
              Sql.Append ("'))) )  ORDER BY A.ID_PARTIDA_EJ, A.SEC_DISTRIB")
           

              dtDistribFI = db.EjecutarConsulta(Sql.ToString (), pm, sMsgError)

              If Not sMsgError.Equals("") Then                   
                 Return New datatable ()
              End If

              Return dtDistribFI

          Catch ex As Exception    
              Return New datatable ()
          End Try



         End Function        
        

    
    #End Region


  #Region "ACTUALIZACIÓN DE FOLIOS DECALOG"


   Public Shared Function ObtenerPEDDistribGeneral(ByVal sFechaIni As String, ByVal sFechaFin As String, ByRef sMsgError As String) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)        
        


        Dim sql  = New StringBuilder ()

        Dim dtDistrib As New DataTable()

        Try
            
        
              sql.Append(" SELECT ")
              sql.Append(" D.ID_PARTIDA_PS,A.ID_PARTIDA_EJ,A.SEC_DISTRIB,A.FEC_REGISTRO, ")
              sql.Append(" E.VALOR AS TVNG,D.PLAZO,D.CANTIDAD_SOL,F.VALOR AS INTERMEDIARIO,A.CANTIDAD_DISTRIBUIDA, ")
              sql.Append(" B.TASA, B.FLEXIBLE, ")
              sql.Append(" A.ENVIADO_DECALOG, isnull(A.FOLIO_ORDEN,'') as FOLIO_ORDEN, ")
              sql.Append(" isnull(A.NOM_ARCHIVO,'') as NOM_ARCHIVO, isnull(A.OBSERVACION_ENV_DEC,'') as OBSERVACION_ENV_DEC, G.VALOR AS ESTATUS  ")
              sql.Append(" FROM FL_OP_DISTRIB_FONDOINTER A ")
              sql.Append(" INNER JOIN FL_OP_PEJ B ON (A.ID_PARTIDA_EJ = B.ID_PARTIDA_EJ ) ")
              sql.Append(" INNER JOIN FL_REL_PS_PEJ C ON (B.ID_PARTIDA_EJ = C.ID_PARTIDA_EJ) ")
              sql.Append(" INNER JOIN FL_OP_PS D ON (C.ID_PARTIDA_PS = D.ID_PARTIDA_PS) ")
              sql.Append(" INNER JOIN GEN_CAT_GEN E ON (D.CAT_TVNG = E.ID_CAT AND D.TVNG = E.CLAVE) ")
              sql.Append(" INNER JOIN GEN_CAT_GEN F ON (B.CAT_INTERMEDIARIO = F.ID_CAT  AND B.INTERMEDIARIO = F.CLAVE) ")
              sql.Append(" INNER JOIN GEN_CAT_GEN G ON (A.ESTATUS_RECUP_FOLIO = G.CLAVE AND G.ID_CAT =  ")
              sql.Append(__CLAVE_CAT_ESTATUS_FOLIO_DEC__)
              sql.Append(") WHERE  ")
              sql.Append(" A.FEC_REGISTRO BETWEEN '")

              sql.Append(Convert.ToDateTime(sFechaIni).ToString("yyyy-MM-dd HH:mm:ss"))
              sql.Append("' AND '")
              sql.Append(Convert.ToDateTime(sFechaFin).ToString("yyyy-MM-dd HH:mm:ss"))
              sql.Append("' ORDER BY 1,2,3 ASC")

              

                
            dtDistrib = db.EjecutarConsulta(sql.ToString (), pm, sMsgError)

            If Not sMsgError.Equals("") Then               
               Return Nothing 
            End If

            Return dtDistrib

        Catch ex As Exception    
             sMsgError = ex.Message 
            Return Nothing 
        End Try



    End Function

   Public Shared Function ObtenerPEDDistribParaActualizarFolioDec(ByVal sFechaIni As String, ByVal sFechaFin As String, ByRef sMsgError As String, ByRef  eventoBit As CSBitEvento ) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)        
        

        Dim sql  = New StringBuilder ()

        Dim dtDistribActualizar As New DataTable()

        Try
            
        
              sql.Append(" SELECT A.ID_PARTIDA_EJ, A.SEC_DISTRIB, A.NOM_ARCHIVO ")
              sql.Append(" FROM FL_OP_DISTRIB_FONDOINTER A ")
              sql.Append(" WHERE ")
              sql.Append(" A.ESTATUS_RECUP_FOLIO =  ")
              sql.Append(CTDistribucionGL.enumEstatusFolioRecupDecalog.NO_RECUPERADO)
              sql.Append(" AND A.NOM_ARCHIVO IS not NULL ")
              
              sql.Append(" AND A.FEC_REGISTRO BETWEEN '")
              sql.Append(Convert.ToDateTime(sFechaIni).ToString("yyyy-MM-dd HH:mm:ss"))
              sql.Append("' AND '")
              sql.Append(Convert.ToDateTime(sFechaFin).ToString("yyyy-MM-dd HH:mm:ss"))
              sql.Append("'")


                
            dtDistribActualizar = db.EjecutarConsulta(sql.ToString (), pm, sMsgError)


            If Not sMsgError.Equals("") Then               
               Return Nothing 
            End If

            Return dtDistribActualizar

        Catch ex As Exception    
             
              eventoBit.GuardarError (ex.Message) 
              sMsgError = ex.Message 
            Return Nothing 
        End Try



    End Function

   Public Shared Function ActualizarFoliosDecalogDB(ByRef lstInfoFoldec As List(Of CTDistribucionGL), ByRef sMsgError As String) As Boolean

      Dim db      As New CBaseDeDatosSQLServer()
      Dim pm      As New List(Of SqlParameter)         
      Dim regAfec As Integer = 0     
                       
      Dim sql        = New  StringBuilder ("")

      For Each reg As CTDistribucionGL In  lstInfoFoldec 
         
         sql.Append(" UPDATE FL_OP_DISTRIB_FONDOINTER SET OBSERVACION_ENV_DEC= '")
                  
         sql.Append(CUtileria.PrepararCadenaParaSQL(reg.Observacion,__MAXLONGITUDMSG__ ))
                                    
         sql.Append("', ESTATUS_RECUP_FOLIO=")
         sql.Append(reg.EstatusRecupFolDec)
         sql.Append(", FOLIO_ORDEN = '")
         sql.Append(reg.FolioOrden)
         sql.Append("' WHERE ID_PARTIDA_EJ = ")         
         sql.Append(reg.PartidaEJ) 
         sql.Append(" AND SEC_DISTRIB = ")         
         sql.Append(reg.SecDistrib)
         sql.Append(";")
         sql.AppendLine 

      Next
      
      If db.EjecutarComando(Sql.ToString() , pm,regAfec , sMsgError) = True Then            
            Return True
      Else
            Return false       
      End If

    End Function

    Public Shared Function IntermPorDistribuir(ByVal IdPs As Long) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dt As New DataTable()

        Try
            sSql = "select inter.VALOR, pe.TASA, dp.CANTIDAD_X_DISTRIB " _
                   & " from FL_REL_DISTRIB_PEJ dp, FL_REL_PS_PEJ rp, FL_OP_PEJ pe, GEN_CAT_GEN inter " _
                   & " where dp.ID_PARTIDA_EJ = rp.ID_PARTIDA_EJ " _
                   & " and pe.ID_PARTIDA_EJ = rp.ID_PARTIDA_EJ " _
                   & " and pe.CAT_INTERMEDIARIO = inter.ID_CAT " _
                   & " and pe.INTERMEDIARIO = inter.CLAVE " _
                   & " and dp.CANTIDAD_X_DISTRIB > 0 " _
                   & " and rp.ID_PARTIDA_PS = " & IdPs.ToString() _
                   & " order by inter.VALOR, pe.TASA, dp.CANTIDAD_X_DISTRIB"


            dt = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dt

        Catch ex As Exception
            Throw ex
        End Try

    End Function
    
    Public Shared Function OrdenesSinEnvioDecalog(ByVal IdPs As Long) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dt As New DataTable()

        Try
            sSql = "select inter.VALOR, pe.TASA, df.CANTIDAD_DISTRIBUIDA " _
                   & " from FL_OP_DISTRIB_FONDOINTER df, FL_REL_PS_PEJ rp, FL_OP_PEJ pe, GEN_CAT_GEN inter " _
                   & " where df.ID_PARTIDA_EJ = rp.ID_PARTIDA_EJ " _
                   & " and pe.ID_PARTIDA_EJ = rp.ID_PARTIDA_EJ " _
                   & " and pe.CAT_INTERMEDIARIO = inter.ID_CAT " _
                   & " and pe.INTERMEDIARIO = inter.CLAVE " _
                   & " and df.ENVIADO_DECALOG = 0 " _
                   & " and df.FOLIO_ORDEN is null " _
                   & " and rp.ID_PARTIDA_PS = " & IdPs.ToString() _
                   & " order by inter.VALOR, pe.TASA, df.CANTIDAD_DISTRIBUIDA"


            dt = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dt

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Shared Function OrdenesSinFolio(ByVal IdPs As Long) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dt As New DataTable()

        Try
            sSql = "select inter.VALOR, pe.TASA, df.CANTIDAD_DISTRIBUIDA " _
                   & " from FL_OP_DISTRIB_FONDOINTER df, FL_REL_PS_PEJ rp, FL_OP_PEJ pe, GEN_CAT_GEN inter " _
                   & " where df.ID_PARTIDA_EJ = rp.ID_PARTIDA_EJ " _
                   & " and pe.ID_PARTIDA_EJ = rp.ID_PARTIDA_EJ " _
                   & " and pe.CAT_INTERMEDIARIO = inter.ID_CAT " _
                   & " and pe.INTERMEDIARIO = inter.CLAVE " _
                   & " and df.ENVIADO_DECALOG = 1 " _
                   & " and df.FOLIO_ORDEN is null " _
                   & " and rp.ID_PARTIDA_PS = " & IdPs.ToString() _
                   & " order by inter.VALOR, pe.TASA, df.CANTIDAD_DISTRIBUIDA"


            dt = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dt

        Catch ex As Exception
            Throw ex
        End Try

    End Function

   #End Region


#Region "CATALOGO FONDOS"


     Public Shared Function ObtenerFondosIndeval() As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sql  = New StringBuilder ()
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()
    

        sql.Append (" SELECT FONDO, CTO_INDEVAL FROM FL_CAT_FONDO ")
      

        dtFondo = db.EjecutarConsulta(sql.ToString (), pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Return Nothing 
        End If

        Return dtFondo

    End Function

     Public Shared Function ObtenerTVNGxFondo(ByVal fondo As String) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sql  = New StringBuilder ()
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()
    

        sql.Append (" SELECT  A.CLAVE AS ID_TVNG, A.VALOR AS TVNG, CASE WHEN B.FONDO IS NULL THEN 0 WHEN B.FONDO IS NOT NULL THEN 1 END AS SELECCIONADO  ")
        sql.Append (" FROM (SELECT CLAVE, VALOR FROM GEN_CAT_GEN  WHERE ID_CAT = ")
        sql.Append (__CLAVE_CAT_TVNG__)
        sql.Append (" ) A LEFT OUTER JOIN (SELECT ID_TVNG, FONDO FROM FL_REL_FONDO_TVNG where FONDO = '")
        sql.Append (fondo)
        sql.Append ("') B ON (A.CLAVE = B.ID_TVNG) ")

        dtFondo = db.EjecutarConsulta(sql.ToString (), pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Return Nothing 
        End If

        Return dtFondo

    End Function

     Public Shared Function ObtenerIntermediarioxFondo(ByVal fondo As String) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sql  = New StringBuilder ()
        Dim sMsgError As String = ""

        Dim dtFondo As New DataTable()
    

        sql.Append (" SELECT A.CLAVE AS ID_INTERMEDIARIO, A.VALOR AS INTERMEDIARIO, CASE WHEN B.FONDO IS NULL THEN 0 WHEN B.FONDO IS NOT NULL THEN 1 END AS SELECCIONADO, isnull(B.CTO_INTERMEDIARIO,'') as CTO_INTERMEDIARIO ")
        sql.Append (" FROM (SELECT CLAVE, VALOR FROM GEN_CAT_GEN  WHERE ID_CAT = ")
        sql.Append (__CLAVE_CAT_INTERMEDIARIO__)
        sql.Append (" ) A LEFT OUTER JOIN (SELECT ID_INTERMEDIARIO, FONDO, CTO_INTERMEDIARIO FROM FL_REL_FONDO_INTERMEDIARIO where FONDO = '")
        sql.Append (fondo)
        sql.Append ("') B ON (A.CLAVE = B.ID_INTERMEDIARIO) ")

        dtFondo = db.EjecutarConsulta(sql.ToString (), pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Return Nothing 
        End If

        Return dtFondo

    End Function


     Public Shared Function ActualizarRelIntermediarioFondo(ByRef lstInterFondo As List(Of CTFondoIntermediario ), ByVal fondo As String, ByRef sMsgError As String) As Boolean
        Dim db      As New CBaseDeDatosSQLServer()
        Dim pm      As New List(Of SqlParameter)         
        Dim regAfec As Integer = 0     
                       
        Dim sql     = New  StringBuilder ("")
            
        sql.Append(" DELETE FROM FL_REL_FONDO_INTERMEDIARIO WHERE FONDO = '")                 
        sql.Append(fondo)
        sql.Append("';")                                                
        sql.AppendLine 
            
        For Each fondoInter As CTFondoIntermediario In lstInterFondo 

             If fondoInter.EsSeleccionado = True then 

                   sql.Append("INSERT INTO FL_REL_FONDO_INTERMEDIARIO (FONDO,CAT_INTERMEDIARIO,ID_INTERMEDIARIO,CTO_INTERMEDIARIO) VALUES ('")
                   sql.Append(fondo)
                   sql.Append("',")
                   sql.Append(__CLAVE_CAT_INTERMEDIARIO__)
                   sql.Append(",")
                   sql.Append(fondoInter.IdIntermediario)
                   sql.Append(",'")
                   sql.Append(fondoInter.Contrato)
                   sql.Append("');")
                   sql.AppendLine 
             End If

        Next


         If db.EjecutarComando(Sql.ToString() , pm,regAfec , sMsgError) = True Then            
               Return True
         Else
               Return false       
         End If

        Return true
     End Function

     Public Shared Function ActualizarRelTVNGFondo(ByRef lstTvngFondo As List(Of CTFondoTVNG), ByVal fondo As String, ByRef sMsgError As String) As Boolean
        Dim db      As New CBaseDeDatosSQLServer()
        Dim pm      As New List(Of SqlParameter)         
        Dim regAfec As Integer = 0     
                       
        Dim sql     = New  StringBuilder ("")
            
        sql.Append(" DELETE FROM FL_REL_FONDO_TVNG WHERE FONDO = '")                 
        sql.Append(fondo)
        sql.Append("';")                                                
        sql.AppendLine 
            
        For Each fondoTVNG As CTFondoTVNG In lstTvngFondo 

             If fondoTVNG.EsSeleccionado = True then 

                   sql.Append("INSERT INTO FL_REL_FONDO_TVNG (FONDO,CAT_TVNG,ID_TVNG) VALUES ('")
                   sql.Append(fondo)
                   sql.Append("',")
                   sql.Append(__CLAVE_CAT_TVNG__)
                   sql.Append(",")
                   sql.Append(fondoTVNG.IdTVNG)                                      
                   sql.Append(");")
                   sql.AppendLine 
             End If

        Next


         If db.EjecutarComando(Sql.ToString() , pm,regAfec , sMsgError) = True Then            
               Return True
         Else
               Return false       
         End If

        Return true
     End Function

    
    
     Public Shared Function ActualizarFondoIndeval(ByRef fondo As string, regCambio As CTFondoIndeval, ByRef sMsgError As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer

        sSql = " UPDATE FL_CAT_FONDO SET  FONDO = '" & regCambio.Fondo    & "',CTO_INDEVAL = '" _
                                                     & regCambio.Indeval  & "' WHERE FONDO = '" _
                                                     & fondo    & "'; " 


        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function
   
     Public Shared Function DarDeAltaFondoIndeval(ByRef regNvo As CTFondoIndeval, ByRef sMsgError As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer


        sSql = " INSERT INTO FL_CAT_FONDO (FONDO,CTO_INDEVAL) VALUES ( '" & regNvo.Fondo    & "', '" _
                                                                          & regNvo.Indeval  & "');"

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If


    End Function

     Public Shared Function EliminarFondoIndeval(ByRef regActual As CTFondoIndeval, ByRef sMsgError As String) As Boolean

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer



        sSql = " DELETE FROM FL_CAT_FONDO WHERE  FONDO = '" & regActual.Fondo & "';" 
                                                             
        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

  

#End Region




    Public Shared Function ObtieneDireccionMails(ByVal IdInter As Integer) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtCatalogo As New DataTable()

        Try
            sSql = "SELECT * FROM FL_PARAM_CARTA_ENVIO WHERE ID_INTERMEDIARIO = " & IdInter
            
            dtCatalogo = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtCatalogo

        Catch ex As Exception
    
            Throw ex
        End Try

    End Function

    Public Shared Function GuardarMails(ByVal idIntermediario As Integer, ByRef listaMails As List(Of CTParamCartaEnvio), ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer


        sSql = "delete FL_PARAM_CARTA_ENVIO where ID_INTERMEDIARIO = " & idIntermediario
        For Each mail As CTParamCartaEnvio In listaMails
            sSql = sSql & "; insert into FL_PARAM_CARTA_ENVIO (CAT_INTERMEDIARIO, ID_INTERMEDIARIO, DIRECCION_MAIL) values (6," & idIntermediario & ",'" & mail.DireccionMail & "')"
        Next

        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function CargarFechasDecalog(ByRef dtDatos As DataTable, ByVal fechaInicio As Date, ByVal fechaFin As Date, ByRef sMsgError As String) As Boolean



        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim query = New StringBuilder("")
        Dim regAfec As Integer
        Try
            
            If dtDatos.Rows.Count > 0 Then
                Dim fecha As Date
                query.AppendLine("delete MMA_CAT_FECHA_NO_LAB where id_funcion = 1 and fecha between '" & fechaInicio.ToString("yyyy-MM-dd") & "' and '" & fechaFin.ToString("yyyy-MM-dd") & "';")
                For Each dt As DataRow In dtDatos.Rows
                    fecha = Convert.ToDateTime(dt("DAT_REF"))
                    query.AppendLine("insert into MMA_CAT_FECHA_NO_LAB (id_funcion, fecha) values (1,'" & fecha.ToString("yyyy-MM-dd") & "');")
                Next
            End If


            If db.EjecutarComando(query.ToString(), pm, regAfec, sMsgError) = True Then
                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Shared Function ReactivarDistribucion(ByVal idPs As Integer, ByVal idPej As Integer, ByVal Sec As Integer, ByRef sMsgError As String) As Boolean
        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim regAfec As Integer
        
        sSql = "update FL_REL_BSO_PS " _
             & "set ESTATUS_PARTIDA = " & CTPartidaSolicitud.enumEstatusPS.EN_DISTRIBUCION _
             & " where ID_PARTIDA_PS = " & idPS

        sSql = sSql & "; update FL_OP_DISTRIB_FONDOINTER " _
               & " set FOLIO_ORDEN = NULL, ENVIADO_DECALOG = 0, NOM_ARCHIVO = NULL, OBSERVACION_ENV_DEC = '', " _
               & " ESTATUS_RECUP_FOLIO = " & CTDistribucionGL.enumEstatusFolioRecupDecalog.NO_RECUPERADO _
               & " where ID_PARTIDA_EJ = " & idPej & " and SEC_DISTRIB = " & Sec
        
        If db.EjecutarComando(sSql, pm, regAfec, sMsgError) = True Then
            Return True
        Else
            Return False
        End If

    End Function




    Public Shared Function ObtieneFondosLiquidezNegocio(ByRef lstNegocio As List(Of Integer)) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dtPS As New DataTable()
        Try
            sSql = "select ctos.FONDO " _
                   & "from FL_REL_FONDO_TVNG ctos " _
                   & "where ctos.ID_TVNG  in (" & GenerarCondicionNegocio(lstNegocio) & ") " _
                   & "group by ctos.FONDO " _
                   & "order by ctos.FONDO"


            dtPS = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtPS

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Private Shared Function GenerarCondicionNegocio(ByRef lstTpNegocio As List(Of Integer)) As String

        Dim negocios As New System.Text.StringBuilder
        Try

            For Each idNeg As Integer In lstTpNegocio
                negocios.Append("'" & idNeg.ToString & "',")
            Next

            negocios.Remove(negocios.Length - 1, 1)
            Dim aux As String = negocios.ToString()
            Return aux
        Catch ex As Exception
            Return ""
        End Try


    End Function

    Public Shared Function ObtieneEncabezadosInterm(ByRef lstNegocio As List(Of Integer), ByVal fecha As String) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dt As New DataTable()
        Try
            sSql = "select case when ps.TVNG <= 4 then 'R' else 'B' end + ' - ' + gi.VALOR + ' - ' + convert(varchar,pe.TASA) intermediario " _
                   & "from FL_OP_DISTRIB_FONDOINTER df, FL_OP_PEJ pe, FL_REL_PS_PEJ pspe, FL_OP_PS ps, FL_OP_CANTIDADXFONDO cf, GEN_CAT_GEN gi " _
                   & "where convert(varchar,df.FEC_REGISTRO, 103) = '" & fecha & "' " _
                   & "and df.ENVIADO_DECALOG = 1 " _
                   & "and pe.ID_PARTIDA_EJ = df.ID_PARTIDA_EJ " _
                   & "and cf.ID_PARTIDA_EJ = df.ID_PARTIDA_EJ " _
                   & "and cf.SEC_DISTRIB = df.SEC_DISTRIB " _
                   & "and gi.ID_CAT = pe.CAT_INTERMEDIARIO " _
                   & "and gi.CLAVE = pe.INTERMEDIARIO " _
                   & "and pspe.ID_PARTIDA_EJ = pe.ID_PARTIDA_EJ " _
                   & "and pspe.ID_PARTIDA_PS = ps.ID_PARTIDA_PS " _
                   & "and ps.TVNG in (" & GenerarCondicionNegocio(lstNegocio) & ") " _
                   & "group by ps.TVNG, gi.VALOR, pe.TASA " _
                   & "order by 1 "


            dt = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dt

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Public Shared Function ObtieneLiqInterm(ByRef lstNegocio As List(Of Integer), ByVal fecha As String) As DataTable

        Dim db As New CBaseDeDatosSQLServer()
        Dim pm As New List(Of SqlParameter)
        Dim sSql As String
        Dim sMsgError As String = ""

        Dim dt As New DataTable()
        Try
            sSql = "select case when ps.TVNG <= 4 then 'R' else 'B' end + ' - ' + gi.VALOR + ' - ' + convert(varchar,pe.TASA) intermediario, " _
                   & "cf.FONDO fondo, sum(cf.CANTIDAD) total, " _
                   & "convert(date, ps.FEC_SOLICITUD) fecha_sol, convert(date, ps.FEC_LIQUIDACION) fecha_liq " _
                   & "from FL_OP_DISTRIB_FONDOINTER df, FL_OP_PEJ pe, FL_REL_PS_PEJ pspe, FL_OP_PS ps, FL_OP_CANTIDADXFONDO cf, GEN_CAT_GEN gi " _
                   & "where convert(varchar,df.FEC_REGISTRO, 103) = '" & fecha & "' " _
                   & "and df.ENVIADO_DECALOG = 1 " _
                   & "and pe.ID_PARTIDA_EJ = df.ID_PARTIDA_EJ " _
                   & "and cf.ID_PARTIDA_EJ = df.ID_PARTIDA_EJ " _
                   & "and cf.SEC_DISTRIB = df.SEC_DISTRIB " _
                   & "and gi.ID_CAT = pe.CAT_INTERMEDIARIO " _
                   & "and gi.CLAVE = pe.INTERMEDIARIO " _
                   & "and pspe.ID_PARTIDA_EJ = pe.ID_PARTIDA_EJ " _
                   & "and pspe.ID_PARTIDA_PS = ps.ID_PARTIDA_PS " _
                   & "and ps.TVNG in (" & GenerarCondicionNegocio(lstNegocio) & ") " _
                   & "group by ps.TVNG, gi.VALOR, pe.TASA, cf.FONDO, " _
                   & "convert(date, ps.FEC_SOLICITUD), convert(date, ps.FEC_LIQUIDACION) " _
                   & "order by 1 "


            dt = db.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dt

        Catch ex As Exception
            Throw ex
        End Try

    End Function

End Class
